Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 46.8Mb
PDF (A4) - 46.9Mb
PDF (RPM) - 42.2Mb
HTML Download (TGZ) - 10.8Mb
HTML Download (Zip) - 10.8Mb
HTML Download (RPM) - 9.3Mb
Man Pages (TGZ) - 226.1Kb
Man Pages (Zip) - 331.5Kb
Info (Gzip) - 4.2Mb
Info (Zip) - 4.2Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  InnoDB and FOREIGN KEY Constraints

15.6.1.5 InnoDB and FOREIGN KEY Constraints

How the InnoDB storage engine handles foreign key constraints is described under the following topics in this section:

For foreign key usage information and examples, see Section 13.1.20.6, “Using FOREIGN KEY Constraints”.

Foreign Key Definitions

Foreign key definitions for InnoDB tables are subject to the following conditions:

  • InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order. Hidden columns that InnoDB adds to an index are also considered (see Section 15.6.2.1, “Clustered and Secondary Indexes”).

  • InnoDB does not currently support foreign keys for tables with user-defined partitioning. This means that no user-partitioned InnoDB table may contain foreign key references or columns referenced by foreign keys.

  • InnoDB allows a foreign key constraint to reference a nonunique key. This is an InnoDB extension to standard SQL.

Referential Actions

Referential actions for foreign keys of InnoDB tables are subject to the following conditions:

  • While SET DEFAULT is allowed by the MySQL Server, it is rejected as invalid by InnoDB. CREATE TABLE and ALTER TABLE statements using this clause are not allowed for InnoDB tables.

  • 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 NO ACTION 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.

  • If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like NO ACTION. 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.

  • Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB 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. Until InnoDB implements deferred constraint checking, some things are impossible, such as deleting a record that refers to itself using a foreign key.

Foreign Key Restrictions for Generated Columns and Virtual Indexes
  • A foreign key constraint on a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE referential actions, nor can it use SET NULL or SET DEFAULT as ON DELETE referential actions.

  • A foreign key constraint on the base column of a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE or ON DELETE referential actions.

  • A foreign key constraint cannot reference a virtual generated column.

  • Prior to MySQL 8.0, a foreign key constraint cannot reference a secondary index defined on a virtual generated column.