Foreign Keys and mysql

Many developers and DBAs who spent their lives on M$ SQL or Oracle turn up their noses at mysql, they refer to it with disdain. According to them the lack of foreign key support in mysql means it is not a proper RBMS and not standard compliant. Nothing can be further from the truth.

Mysql has supported foreign keys for a long time now many people simply don't know of it or use it because foriegn keys cannot be used with MyISAM type of tables. You need to use InnoDB type tables instead.

Since many of the mysql tutorials on the web were written long long ago before InnoDB support was added, they rarely refer to the use of this table type or foreign keys. Fortunately it's a trival matter to convert your tables from MyISAM to InnoDB - provided that their data integrity has not been violated.

ALTER TABLE tablename TYPE=InnoDB is all that is needed to perform the conversion. Now you are ready to add foreign keys - let's look at an example.

alter table userProfile add Foreign key (userId) REFERENCES users(userId);

This foreign key is useful as it can stop you entering a User Profile for a non existent user. Even more useful is to have a foreign key that results in an unwanted user's profile being deleted when you delete the entry in the user's table. You an also prevent a use from being deleted at all if another table refers to that user. Before we create such a foreign key, let's drop the old one we created.

alter table userProfile drop foreign key userId;

Beware; this drop foreign key statement will fail if there are inconsistencies in your data. The only remedy then is to create a new table do an INSERT .. SELECT query , drop the original table and rename the new table to use the old table name then take a deep breath.

Before we were so rudely disturbed, we were discussing cascading deletes, here is how you should create your foreign key if you want to make use of this feature:

alter table userProfile add constraint foreign key (userId) references users(userId) on delete cascade;