Skip navigation links
1.9.5.6 Views »
Section Navigation      [Toggle]

1.9.5.5. Foreign Keys

The InnoDB storage engine supports checking of foreign key constraints, including CASCADE, ON DELETE, and ON UPDATE. See Section 13.2.5.4, “FOREIGN KEY Constraints”.

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.

Foreign key enforcement offers several benefits to database developers:

  • Assuming proper design of the relationships, foreign key constraints make it more difficult for a programmer to introduce an inconsistency into the database.

  • Centralized checking of constraints by the database server makes it unnecessary to perform these checks on the application side. This eliminates the possibility that different applications may not all check the constraints in the same way.

  • Using cascading updates and deletes can simplify the application code.

  • Properly designed foreign key rules aid in documenting relationships between tables.

Do keep in mind that these benefits come at the cost of additional overhead for the database server to perform the necessary checks. Additional checking by the server affects performance, which for some applications may be sufficiently undesirable as to be avoided if possible. (Some major commercial applications have coded the foreign key logic at the application level for this reason.)

MySQL gives database developers the choice of which approach to use. If you don't need foreign keys and want to avoid the overhead associated with enforcing referential integrity, you can choose another storage engine instead, such as MyISAM. (For example, the MyISAM storage engine offers very fast performance for applications that perform only INSERT and SELECT operations. In this case, the table has no holes in the middle and the inserts can be performed concurrently with retrievals. See Section 7.3.3, “Concurrent Inserts”.)

If you choose not to take advantage of referential integrity checks, keep the following considerations in mind:

  • In the absence of server-side foreign key relationship checking, the application itself must handle relationship issues. For example, it must take care to insert rows into tables in the proper order, and to avoid creating orphaned child records. It must also be able to recover from errors that occur in the middle of multiple-record insert operations.

  • If ON DELETE is the only referential integrity capability an application needs, you can achieve a similar effect as of MySQL Server 4.0 by using multiple-table DELETE statements to delete rows from many tables with a single statement. See Section 12.2.1, “DELETE Syntax”.

  • A workaround for the lack of ON DELETE is to add the appropriate DELETE statements to your application when you delete records from a table that has a foreign key. In practice, this is often as quick as using foreign keys and is more portable.

Be aware that the use of foreign keys can sometimes lead to problems:

  • Foreign key support addresses many referential integrity issues, but it is still necessary to design key relationships carefully to avoid circular rules or incorrect combinations of cascading deletes.

  • It is not uncommon for a DBA to create a topology of relationships that makes it difficult to restore individual tables from a backup. (MySQL alleviates this difficulty by allowing you to temporarily disable foreign key checks when reloading a table that depends on other tables. See Section 13.2.5.4, “FOREIGN KEY Constraints”. As of MySQL 4.1.1, mysqldump generates dump files that take advantage of this capability automatically when they are reloaded.)

Foreign keys in SQL are used to check and enforce referential integrity, not to join tables. If you want to get results from multiple tables from a SELECT statement, you do this by performing a join between them: 

SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;

See Section 12.2.7.1, “JOIN Syntax”, and Section 3.6.6, “Using Foreign Keys”.

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


User Comments

Posted by sukasom chaiyakul on April 8 2005 2:00pm[Delete] [Edit]

CREATE TABLE reviewing
(
reviewingid INTEGER ,
papername text,
reviewername text,
PRIMARY KEY(reviewingid)
);

CREATE TABLE score
(
reviewingid integer,
questionnumber integer,
score integer,
FOREIGN KEY (reviewingid) REFERENCES reviewing (reviewingid) on delete cascade
);

insert into reviewing values (1,"how to survive","John Doe") ;
insert into score values(1,1,5);
delete from reviewing where reviewingid = 1;

Insert the record in the table reviewing, and then insert the record into the table score which reviewingid in table score is referenced to reviewingid in reviewing table.

When deleting the record in the reviewing table, the record in the score with the same reviewerid is also deleted as well, because of the "on delete cascade" clause.

Posted by Clement Soullard on July 25 2006 6:52pm[Delete] [Edit]

But you can also use the simpler definition

-- Posts table
CREATE TABLE post(
id INT PRIMARY KEY,
title VARCHAR(100),
dateissued TIMESTAMP,
datecreated TIMESTAMP,
datemodified TIMESTAMP,
content TEXT,
);

-- Date of publication
CREATE TABLE publish(
id INT,
datepublish TIMESTAMP,
urldest VARCAHR(100),
postid int REFERENCES post(id)
);

Add your own comment.