MySQL adding foreign keys doesn’t work

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 (
id bigint,
name varchar(10),
parent_duck bigint,
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 :).

Advertisements

One Response to MySQL adding foreign keys doesn’t work

  1. Miguel Carneiro says:

    Thanks a bunch. Changing the engine to InnoDB tottaly worked for me!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: