Some reasons NOT to use FOREIGN KEYS

There are so many problems with the FOREIGN KEYs that we don't know where to start.

• The speed impact will be terrible when INSERTING and UPDATING records and in this case almost all FOREIGN KEY checks are useless because one usually inserts records in the right tables in the right order.
• There will also be a need to hold locks on many more tables when updating one because the side effects can cascade trough the hole database. It's MUCH faster to first delete records from one table and subsequently delete them from the other tables.
• One can't restore a table anymore by doing a full delete from the table and then restoring all records (from a new source or from a backup).
• If you have foreign keys you can't dump and restore tables without doing this in a very specific order.
• It's very easy to do 'allowed' circular definitions that make the tables impossible to recreate with a single create statement, even if the definition works and is usable.

The only nice aspect of foreign key is that it gives ODBC and some other client programs the ability to see how a table is connected and use this to show connection diagrams and to help building applicatons.

MySQL will soon store the FOREIGN KEY definitions so that a client can ask and receive an answer how the original connection was made. The current .frm file format does not have any place for it.

Go to the first, previous, next, last section, table of contents.