Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 44.6Mb
PDF (A4) - 44.6Mb
PDF (RPM) - 40.3Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.5Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 204.6Kb
Man Pages (Zip) - 311.5Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

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

15.6.1.6 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 listed as the first columns in the same order.

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

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

  • 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 ON UPDATE CASCADE, ON DELETE SET NULL, ON UPDATE SET NULL, ON DELETE SET DEFAULT, or ON UPDATE SET DEFAULT.

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


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Christopher Beland on April 11, 2011
Note that it will fail if you try to insert data into a table that has a foreign key constraint where the foreign table is a view.
  Posted by David Filmer on June 5, 2011
If you get an error:
<b>ERROR 1216: Cannot add or update a child row: a foreign key constraint fails</b>
it means you have two tables, at least one of which contains data, and you are trying to establish a relationship between keys (such as table1.id = table2.id), but there is data in one or both tables which does not currently meet this condition (often because table1 might contain keys that table2 does not contain and/or vice-versa).
You can't force a relationship onto tables which are not already compliant with the constraint (nor would you want to - if you are trying to do this, you haven't thought it out).

  Posted by manish patel on July 12, 2011
========To Remove Foreign Key from child table==========

CREATE TABLE parent (id INT NOT NULL, name varchar(25) not null default '',
PRIMARY KEY `id_name` (id,name)
) ENGINE=INNODB;

CREATE TABLE child (id INT, parent_id INT, parent_name varchar(25) not null default '',
INDEX par_ind (parent_id),
FOREIGN KEY `id_name` (parent_id, parent_name) REFERENCES parent(id,name)
ON Delete CASCADE
on update CASCADE

) ENGINE=INNODB;

#show create table schema of child table
show create table child;

CREATE TABLE `child` (
`id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`parent_name` varchar(25) NOT NULL DEFAULT '',
KEY `par_ind` (`parent_id`),
KEY `id_name` (`parent_id`,`parent_name`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`, `parent_name`) REFERENCES `parent` (`id`, `name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

then use CONSTRAINT `child_ibfk_1` to drop as Foreign key from child table by using below query.

alter table child drop FOREIGN KEY child_ibfk_1;

  Posted by Markus Zeller on January 5, 2012
I had the same issue and it could be solved by checking the keys to be *exactly* the same.

In my case I had the parent table with int unsigned not null, and the child had int signed not null.

So the signing was causing the error. I changed the child to be unsigned (just a unwanted mistake on creating the table) and all went fine.
  Posted by Philip Flammer on June 18, 2012
When you get errors like the errno 150, and errno 121, and you don't have SUPER privileges to run SHOW ENGINE INNODB STATUS, it can take a long time to debug as a number of people above have discussed. Here is a list of known causes and solutions to various foreign key errors including these:

http://eliacom.com/wpErrNo150.php
  Posted by Aurelien Marchand on February 27, 2013
INSERT IGNORE will *still* trip the foreign key constraint. It's a known bug that is to be fixed in a later version.

CREATE TABLE T1 (id1 int, index key(id1));
CREATE TABLE T2(id2 int, foreign key (id2) references T1(id1));

INSERT INTO T1(1); /* OK */
START TRANSACTION;
INSERT IGNORE INTO T2(2); /* MySQL error, foreign key constraint violated, even though it was marked as IGNORE */
INSERT IGNORE INTO T2(1);
COMMIT;

end result:
T1 contains (1)
T2 is empty!
Sign Up Login You must be logged in to post a comment.