Rechercher dans le manuel MySQL
1.8.2.3 Foreign Key Differences
The MySQL 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 aRESTRICT
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 anInnoDB
extension.If
ON UPDATE CASCADE
orON UPDATE SET NULL
recurses to update the same table it has previously updated during the same cascade, it acts likeRESTRICT
. This means that you cannot use self-referentialON UPDATE CASCADE
orON UPDATE SET NULL
operations. This is to prevent infinite loops resulting from cascaded updates. A self-referentialON DELETE SET NULL
, on the other hand, is possible, as is a self-referentialON 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
Section 15.6.1.5, “InnoDB and FOREIGN KEY Constraints”.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-ansi-diff-foreign-keys.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.