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 relates parent
and
child
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 ROW(1);
Verify that the data was inserted. You can do this simply by
selecting all rows from parent
, as shown
here:
mysql> TABLE parent;
+----+
| id |
+----+
| 1 |
+----+
Insert a row into the child table using the following SQL statement:
mysql> INSERT INTO child (id,parent_id) VALUES ROW(1,1);
The insert operation is successful because
parent_id
1 is present in the parent table.
Insertion of a row into the child table with a
parent_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 ROW(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 specified
parent_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 VALUES = 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 by ON UPDATE
and
ON DELETE
subclauses of the FOREIGN
KEY
clause. Omitting ON DELETE
and
ON UPDATE
clauses (as in the current child
table definition) is the same as specifying the
RESTRICT
option, which rejects operations
that affect a key value in the parent table that has matching
rows in the parent table.
To demonstrate ON DELETE
and ON
UPDATE
referential actions, drop the child table and
recreate it to include ON UPDATE
and
ON DELETE
subclauses with the
CASCADE
option. The
CASCADE
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 ROW(1,1), ROW(2,1), ROW(3,1);
Verify that the data was inserted, like this:
mysql> TABLE 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> TABLE parent;
+----+
| id |
+----+
| 2 |
+----+
Verify that the ON UPDATE CASCADE
referential
action updated the child table, like this:
mysql> TABLE child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
+------+-----------+
To demonstrate the ON DELETE CASCADE
referential action, delete records from the parent table where
parent_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 with
parent_id = 2
, the ON DELETE
CASCADE
referential action removes all records from
the child table, as shown here:
mysql> TABLE child;
Empty set (0.00 sec)
For more information about foreign key constraints, see Section 15.1.20.5, “FOREIGN KEY Constraints”.