how to add foreign key in alter command
Hi guys Please tell me the command syntax, how to add a colmmen foreign key in alter syntax thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Confused about syntax for specific join with 3 tables
I've been doing some experimenting with the data model from the "MySQL"
book (Addison Wesley). I have no trouble understanding joins between
two tables, but I'm finding it's a little confusing when 3 or more
tables are involved. I'm going to cite a particular set of tables and a
specific query. I would have assumed it would need to be one way, but
it actually requires a different approach, which I don't quite understand.
Here are the table creation scripts:
CREATE TABLE student
(
name VARCHAR(20) NOT NULL,
sexENUM('F','M') NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (student_id)
) ENGINE = InnoDB;
CREATE TABLE grade_event
(
date DATE NOT NULL,
category ENUM('T','Q') NOT NULL,
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (event_id)
) ENGINE = InnoDB;
CREATE TABLE score
(
student_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
score INT NOT NULL,
score_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (score_id),
INDEX (student_id),
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE = InnoDB;
So, the query I want to build will list the quiz (not test) scores for a
particular student.
If I were to construct this "logically", I would think the query would
be this:
select score.score
from student left join score inner join grade_event
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
I visualize it as "student" joining to "score" joining to "grade_event".
Unfortunately, this query fails to parse with an unhelpful error message.
The query that works, with the joins out of the order I expected, is the
following:
select score.score
from student inner join grade_event left join score
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
Can someone please go into detail of why what I first tried didn't work,
and why it needs to be the other way?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
On May 16, 2009, at 12:28 AM, Simon J Mudd wrote: I also, in section [mysqld] # Begin slave config 05/14/2009 server-id = 2 master-host = ip.add.re.ss master-user = user-replicate master-password = xx master-port = 3306 # End slave config No. not necessary as the information is stored in the master info file. Thanks Simon, I will test. Looks like if this is the case, literally, 99% of every tutorial out there is wrong, as they all do this in duplicate, along with the "CHANGE MASTER" SQL command. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
On Fri, May 15, 2009 at 12:48:18AM -0700, Scott Haneda wrote: > > Also, how do I set the slave to be read only? I set read-only in > my.cnf and it made all databases read only. SET GLOBAL read_only = true; and as you've done in the my.cnf file. Unless the user has SUPER rights he can't change things in the database. There are some minor exceptions: - you can create temporary tables - you can run ANALYZE TABLE These are normally not an issue. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
On Thu, May 14, 2009 at 04:45:44PM -0700, Scott Haneda wrote: > >It's true that initial mysql replication setup is a bit fiddly, but > >once you've done it once or twice it's not so hard. > > I have it set up and working in test. I will redo it again once I get > a better handle on it. I am still a little confused on one aspect. > In the mysql sample cfg file, the section that has: > #Replication Slave there is a very clear "OR" to use either #1 OR #2. > > I did the suggestions of #2, issuing > #CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=, > #MASTER_USER=, MASTER_PASSWORD= ; > on the slave. Sounds fine. > I also, in section [mysqld] > # Begin slave config 05/14/2009 > server-id = 2 > master-host = ip.add.re.ss > master-user = user-replicate > master-password = xx > master-port = 3306 > # End slave config No. not necessary as the information is stored in the master info file. > Am I correct in that this is not needed. I know I for certain need > server_id, but is that all I need, and I have redundant data? I > figure also better to not have raw user and pass in a cnf file if it > is not needed. The server-id IS needed and MUST be different on each server. ... > log-bin = /usr/local/mysql/var/bin.log This can be in the datadir just fine. If you server is very busy with updates some people recommend putting this on a different filesystem to spread the I/O. Depending on your setup that may or may not help. If you don't need it now don't bother. > log-slave-updates Only needed if you have a daisy-chained replication environment you need this. Without it the salve will only store the commands run on the slave itself thus missing the commands run on the original master. If you want to make a slave from the SLAVE server then without this option you won't pick up all the replication commands. > auto_increment_increment = 10 Unless you are running master-master replication ignore this. > > replicate-do-db = somedbname1 > replicate-do-db = somedbname2 required if you don't want to replicate all the dbs on the server. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
