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
