Movatterモバイル変換

MySQL supports foreign keys, which permit cross-referencing related data across tables, and foreign key constraints, which help keep the related data consistent.

A foreign key relationship involves a parent table that holds the initial column values, and a child table with column values that reference the parent column values. A foreign key constraint is defined on the child table.

This following example relatesparent andchild tables through a single-column foreign key and shows how a foreign key constraint enforces referential integrity.

Create the parent and child tables using the following SQL statements:

CREATE TABLE parent (    id INT NOT NULL,    PRIMARY KEY (id)) ENGINE=INNODB;CREATE TABLE child (    id INT,    parent_id INT,    INDEX par_ind (parent_id),    FOREIGN KEY (parent_id)        REFERENCES parent(id)) ENGINE=INNODB;

Insert a row into the parent table, like this:

mysql> INSERT INTO parent (id) VALUES (1);

Verify that the data was inserted. You can do this simply by selecting all rows fromparent, as shown here:

mysql> SELECT * FROM parent;+----+| id |+----+|  1 |+----+

Insert a row into the child table using the following SQL statement:

mysql> INSERT INTO child (id,parent_id) VALUES (1,1);

The insert operation is successful becauseparent_id 1 is present in the parent table.

Insertion of a row into the child table with aparent_id value that is not present in the parent table is rejected with an error, as you can see here:

mysql> INSERT INTO child (id,parent_id) VALUES(2,2);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

The operation fails because the specifiedparent_id value does not exist in the parent table.

Trying to delete the previously inserted row from the parent table also fails, as shown here:

mysql> DELETE FROM parent WHERE id = 1;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

This operation fails because the record in the child table contains the referenced id (parent_id) value.

When an operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified byON UPDATE andON DELETE subclauses of theFOREIGN KEY clause. OmittingON DELETE andON UPDATE clauses (as in the current child table definition) is the same as specifying theRESTRICT option, which rejects operations that affect a key value in the parent table that has matching rows in the parent table.

To demonstrateON DELETE andON UPDATE referential actions, drop the child table and recreate it to includeON UPDATE andON DELETE subclauses with theCASCADE option. TheCASCADE option automatically deletes or updates matching rows in the child table when deleting or updating rows in the parent table.

DROP TABLE child;CREATE TABLE child (    id INT,    parent_id INT,    INDEX par_ind (parent_id),    FOREIGN KEY (parent_id)        REFERENCES parent(id)        ON UPDATE CASCADE        ON DELETE CASCADE) ENGINE=INNODB;

Insert some rows into the child table using the statement shown here:

mysql> INSERT INTO child (id,parent_id) VALUES(1,1),(2,1),(3,1);

Verify that the data was inserted, like this:

mysql> SELECT * FROM child;+------+-----------+| id   | parent_id |+------+-----------+|    1 |         1 ||    2 |         1 ||    3 |         1 |+------+-----------+

Update the ID in the parent table, changing it from 1 to 2, using the SQL statement shown here:

mysql> UPDATE parent SET id = 2 WHERE id = 1;

Verify that the update was successful by selecting all rows from the parent table, as shown here:

mysql> SELECT * FROM parent;+----+| id |+----+|  2 |+----+

Verify that theON UPDATE CASCADE referential action updated the child table, like this:

mysql> SELECT * FROM child;+------+-----------+| id   | parent_id |+------+-----------+|    1 |         2 ||    2 |         2 ||    3 |         2 |+------+-----------+

To demonstrate theON DELETE CASCADE referential action, delete records from the parent table whereparent_id = 2; this deletes all records in the parent table.

mysql> DELETE FROM parent WHERE id = 2;

Because all records in the child table are associated withparent_id = 2, theON DELETE CASCADE referential action removes all records from the child table, as shown here:

mysql> SELECT * FROM child;Empty set (0.00 sec)

For more information about foreign key constraints, seeSection 13.1.18.5, “FOREIGN KEY Constraints”.