Re: InnoDB foreign keys bug
Scott Wong [EMAIL PROTECTED], Do you think the following links may help? * http://www.mysql.com/doc/en/example-Foreign_keys.html * http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html * http://www.mysql.com/doc/en/ALTER_TABLE.html * http://www.mysql.com/doc/en/Using_InnoDB_tables.html * http://www.mysql.com/doc/en/Indexes.html This was an automated response to your email 'InnoDB foreign keys bug'. Final search keyword used to query the manual was 'InnoDB foreign keys bug'. Feedbacks, suggestions and complaints about me should be directed to [EMAIL PROTECTED] - 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
InnoDB foreign keys bug
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
Re: InnoDB foreign keys bug
Scott, I would like to declare this as a 'feature'. You should not define multiple foreign key constraints on the same foreign key/referenced key pair. I could, of course, add an error message if someone tries to do that. The algorithm in ON UPDATE CASCADE and ON DELETE CASCADE is this: InnoDB takes a single declared constraint, tries to update or delete child rows as instructed in that constraint, and checks that other constraints are satisfied. Also, I recommend not to define contradictory ON UPDATE actions. The following is an example of such: CREATE TABLE t (a INT NOT NULL, PRIMARY KEY (a), FOREIGN KEY (a) REFERENCES t2 (a) ON UPDATE CASCADE, FOREIGN KEY (a) REFERENCES t2 (b) ON UPDATE CASCADE) TYPE = InnoDB; Now if someone UPDATEs both a and b in t2, what should we do? Regards, Heikki Innobase Oy sql query . Subject: InnoDB foreign keys bug From: Scott Wong Date: Thu, 6 Feb 2003 15:25:12 -0800 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
