On Thu, 22 Jul 2004 12:14:58 -0400 Roy Harrell <[EMAIL PROTECTED]> wrote:
> Can a child table have multiple foreign key references linking > its records to two or more parent tables? yes - something like: CREATE TABLE child ( p1_id INT, p2_id INT, INDEX p1_id_ind (p1_id), INDEX p2_id_ind (p2_id), FOREIGN KEY (p1_id) REFERENCES parent1(id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (p2_id) REFERENCES parent2(id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (p1_id, p2_id) ); > Can a parent table also be a child table? That is, can a parent > table have a foreign key that links it to another table? Yes - consider the somewhat nonesensical scenario: CREATE TABLE parent1 ( id INT NOT NULL PRIMARY KEY ); CREATE TABLE parent2 ( id INT NOT NULL PRIMARY KEY ); CREATE TABLE child ( p1_id INT, p2_id INT, INDEX p1_id_ind (p1_id), INDEX p2_id_ind (p2_id), FOREIGN KEY (p1_id) REFERENCES parent1(id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (p2_id) REFERENCES parent2(id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (p1_id, p2_id) ); CREATE TABLE grandchild ( p1_id INT, p2_id INT, name VARCHAR(100), INDEX p1_p2_id_ind (p1_id, p2_id), FOREIGN KEY (p1_id, p2_id) REFERENCES child(p1_id, p2_id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (p1_id, p2_id) ); Note that foreign keys can contain multiple columns (as shown above) if the parent table has a multi-column (composite) primary key. > I setting up my tables as INNODB types. Good. All my create tables should have had the ENGINE=InnoDB but I'm lazy. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
