GNU Info

(mysql.info)Broken Foreign KEY

---

Next: Missing Views Prev: Missing Foreign Keys Up: Missing functions

---

Enter node , (file) or (file)node

Reasons NOT to Use Foreign Keys constraints
...........................................

There are so many problems with foreign key constraints that we don't
know where to start:

   * Foreign key constraints make life very complicated, because the
     foreign key definitions must be stored in a database and
     implementing them would destroy the whole "nice approach" of using
     files that can be moved, copied, and removed.

   * The speed impact is terrible for `INSERT' and `UPDATE' statements,
     and in this case almost all `FOREIGN KEY' constraint checks are
     useless because you usually insert records in the right tables in
     the right order, anyway.

   * There is also a need to hold locks on many more tables when
     updating one table, because the side effects can cascade through
     the entire database. It's MUCH faster to delete records from one
     table first and subsequently delete them from the other tables.

   * You can no longer restore a table by doing a full delete from the
     table and then restoring all records (from a new source or from a
     backup).

   * If you use foreign key constraints you can't dump and restore
     tables unless you do so in a very specific order.

   * It's very easy to do "allowed" circular definitions that make the
     tables impossible to re-create each table with a single create
     statement, even if the definition works and is usable.

   * It's very easy to overlook `FOREIGN KEY ... ON DELETE' rules when
     one codes an application. It's not unusual that one loses a lot of
     important information just because a wrong or misused `ON DELETE'
     rule.

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
to use this to show connection diagrams and to help in building
applications.

MySQL will soon store `FOREIGN KEY' definitions so that a client can
ask for and receive an answer about how the original connection was
made. The current `.frm' file format does not have any place for it.
At a later stage we will implement the foreign key constraints for
application that can't easily be coded to avoid them.