Scott,
http://www.innodb.com/ibman.html#InnoDB_foreign_keys
"
Starting from version 3.23.50 you can also associate the ON DELETE CASCADE
or ON DELETE SET NULL clause with the foreign key constraint. Corresponding
ON UPDATE options are available starting from 4.0.8.
"
Regards,
Heikki
Innobase Oy
sql query
........................
Subject: Innodb Foreign Key Problems.
From: Scott Wong
Date: Wed, 5 Feb 2003 10:03:17 -0800
Hi. Simple parent/child table generates some weird output based on the order
possible bug?
Mysql 3.23.54
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent id INT, INDEX par ind (parent id),
FOREIGN KEY (parent id) REFERENCES parent(id)
ON UPDATE CASCADE
ON DELETE CASCADE
) TYPE=INNODB;
show create table commands give this :
FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`)
when it should be
FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE
insert into parent set id = 1;
insert into child set id=1, parent id=1;
delete from parent where id = 1;
ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
Now if the child was created like this :
drop table child;
CREATE TABLE child(id INT, parent id INT, INDEX par ind (parent id),
FOREIGN KEY (parent id) REFERENCES parent(id)
ON DELETE CASCADE
ON UPDATE CASCADE
) TYPE=INNODB;
show create table gives this :
FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE
Order matters! :)
and another bug from same tables: (do this with the create table command
above)
alter table child add FOREIGN KEY (`parent id`) REFERENCES `test.parent`
(`id`)
ON UPDATE CASCADE
ON DELETE CASCADE
alter table child add FOREIGN KEY (`parent id`) REFERENCES `test.parent`
(`id`)
ON DELETE CASCADE --reversed from above
ON UPDATE CASCADE
and you'll get some nice output from show create table:
FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`)
Thank you for your time.
Fix?
Scott Wong,
Meiko America, INC
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
