1.8.4.5 Foreign Keys

In MySQL Server 3.23.44 and up, InnoDB tables support checking of foreign key constraints, including CASCADE, ON DELETE, and ON UPDATE. See section 7.5.5.2 Foreign Key Constraints.

For other table types, MySQL Server only parses the FOREIGN KEY syntax in CREATE TABLE commands, but does not use/store this info.

Note that foreign keys in SQL are not used to join tables, but are used mostly for checking referential integrity (foreign key constraints). If you want to get results from multiple tables from a SELECT statement, you do this by joining tables:

SELECT * FROM table1,table2 WHERE table1.id = table2.id;

See section 6.4.1.1 JOIN Syntax. See section 3.5.6 Using Foreign Keys.

When used as a constraint, FOREIGN KEYs don't need to be used if the application inserts rows into MyISAM tables in the proper order.

For MyISAM tables, you can work around the lack of ON DELETE by adding the appropriate DELETE statement to an application when you delete records from a table that has a foreign key. In practice this is as quick (in some cases quicker) and much more portable than using foreign keys.

In MySQL Server 4.0 you can use multi-table delete to delete rows from many tables with one command. See section 6.4.6 DELETE Syntax.

The FOREIGN KEY syntax without ON DELETE ... is often used by ODBC applications to produce automatic WHERE clauses.

In the near future we will extend the FOREIGN KEY implementation so that the information is stored in the table specification file and may be retrieved by mysqldump and ODBC. At a later stage we will implement foreign key constraints for MyISAM tables as well.

Do keep in mind that foreign keys are often misused, which can cause severe problems. Even when used properly, it is not a magic solution for the referential integrity problem, although it can make things easier.

Some advantages of foreign key enforcement:

• Assuming proper design of the relations, foreign key constraints will make it more difficult for a programmer to introduce an inconsistency into the database.
• Using cascading updates and deletes can simplify the client code.
• Properly designed foreign key rules aid in documenting relations between tables.

Disadvantages:

• Mistakes, which are easy to make in designing key relations, can cause severe problems--for example, circular rules, or the wrong combination of cascading deletes.
• A properly written application will make sure (internally) that it is not violating referential integrity constraints before proceding with a query. Thus, additional checks on the database level will only slow down performance for such an application.
• It is not uncommon for a DBA to make such a complex topology of relations that it becomes very difficult, and in some cases impossible, to back up or restore individual tables.

User Comments

The article sort of rationalizes that referential integrity (foreign key constraints) is not so important but I'm really not sure I agree.

I think the trend for database systems is to become larger, not smaller as years go by, and the decision between performance on the one side and system stability on the other side really keeps moving towards the latter as we deal with bigger applications and want fewer surprises.

I have always built referential integrity wherever it should exist regarding the business logic, with the Oracle and SQL-2000 databases I've been using, and very glad I'm able to do that because all the errors come up as you are testing and if I didn't have the integrity checks then those errors wouldn't have come up but I'd have data that doesn't match the logic and the errors would appear in the stranges places and this is so much harder to debug.

Regarding backing up databases with lots of constraints, I don't see what's wrong with constructing the tables without constraints, adding the data (which you know is consistent because it came from a database with the constraints) and then adding the constraints to the table with ALTER TABLE at the end? I might be wrong for sure but that makes sense to me.

Cheers
Julian Cochran
DigitalScores
http://www.digitalscores.com

... or you could use SET FOREIGN_KEY_CHECKS=0 when loading the data.

Julian Cochran
DigitalScores
http://www.digitalscores.com

"In theory, properly written programs have no bugs". --LOL!

Integrity constraint checking is absolutely indispensible and is properly-located in rules interpreted by the database, no matter what the performance hit.

This will be seen especially in enterprise systems where many different applications update the same database. My application may guarantee never to introduce corruption, but there are hundreds of other programs running.

Placing integrity checks in the database greatly simplifies the assumptions each of these programs are able to make on the data.

Add your own comment.