Deviations from SQL Standards

Deviations from SQL Standards
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 rowlevel
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.
... zobacz całą notatkę