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
