Version: Mysql 4.0.10-gamma Description: Innodb fails to follow the foreign key rules after alter table. This bug can be done in several ways.
How to repeat These variations gives same errors. 1st bug example: drop table if exists parent; drop table if exists child; 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 ) TYPE=INNODB; insert into parent set id = 1; insert into child set id=1, parent_id=1; alter table child add FOREIGN KEY (`parent_id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE ; update parent set id=2 where id=1; gives :ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails end 1st bug. Variations of this bug : drop table if exists parent; drop table if exists child; 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 DELETE CASCADE ON UPDATE CASCADE ) TYPE=INNODB; insert into parent set id = 1; insert into child set id=1, parent_id=1; alter table child add FOREIGN KEY (`parent_id`) REFERENCES `test.parent` (`id`) ON UPDATE CASCADE ; update parent set id=2 where id=1; ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails delete from parent; The intesting thing is this : show create table child; FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON UPDATE CASCADE, FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE fix ? Thank you for your time 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