Documentation Home
MySQL Restrictions and Limitations
Related Documentation Download this Excerpt
PDF (US Ltr) - 418.8Kb
PDF (A4) - 419.2Kb
EPUB - 129.7Kb
HTML Download (TGZ) - 84.0Kb
HTML Download (Zip) - 102.5Kb

10.3 Foreign Key Differences

MySQL's implementation of foreign keys differs from the SQL standard in the following key respects:

  • If there are several rows in the parent table that have the same referenced key value, InnoDB acts in foreign key checks as if the other parent rows with the same key value do not exist. For example, if you have defined a RESTRICT type constraint, and there is a child row with several parent rows, InnoDB does not permit the deletion of any of those parent rows.

    InnoDB performs cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints.

  • A FOREIGN KEY constraint that references a non-UNIQUE key is not standard SQL but rather an InnoDB extension.

  • If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the same cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.

  • In an SQL statement that inserts, deletes, or updates many rows, foreign key constraints (like unique constraints) are checked row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records that it must examine. MySQL checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. This means that it is not possible to delete a row that refers to itself using a foreign key.

For information about how the InnoDB storage engine handles foreign keys, see InnoDB and FOREIGN KEY Constraints.

Download this Excerpt
PDF (US Ltr) - 418.8Kb
PDF (A4) - 419.2Kb
EPUB - 129.7Kb
HTML Download (TGZ) - 84.0Kb
HTML Download (Zip) - 102.5Kb
User Comments
  Posted by sukasom chaiyakul on April 8, 2005
CREATE TABLE reviewing
reviewingid INTEGER ,
papername text,
reviewername text,
PRIMARY KEY(reviewingid)

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
But you can also use the simpler definition

-- Posts table
title VARCHAR(100),
dateissued TIMESTAMP,
datecreated TIMESTAMP,
datemodified TIMESTAMP,
content TEXT,

-- Date of publication
id INT,
datepublish TIMESTAMP,
urldest VARCAHR(100),
postid int REFERENCES post(id)
  Posted by Joe Brown on September 3, 2013
Disable Foreign Key Checks is actually explained in section, Using FOREIGN KEY Constraints:

mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name;
mysql> SET foreign_key_checks = 1;
Sign Up Login You must be logged in to post a comment.