MySQL adding foreign keys doesn’t work
July 8, 2010 1 Comment
I ran into an interesting problem recently where the foreign key constraint on my MySQL table wouldn’t stick. Consider the simple case:
create table duck (
foreign key(parent_duck) references duck(id) on update cascade
Now if you do a show create table. The foreign key constraint is nowhere to be found. What’s the deal? Dig around on MySQL documentatoin and you finally run into(http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-foreign-keys.html)
For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it. In the future, the implementation will be extended to store this information in the table specification file so that it may be retrieved by mysqldump and ODBC. At a later stage, foreign key constraints will be implemented for MyISAM tables as well.
So the way to fix it to declare the table as a InnoDB: alter table duck engine=InnoDB;
Why isn’t InnoDB default and why is MySQL silently ignoring the constraints is anyones guess :).