MySQL 支持外键，它允许在表之间交叉引用相关数据，并且支持外键约束，这有助于保持相关数据的一致性。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.

以下示例通过单列外键关联parent和child表，并展示了外键约束如何强制执行参照完整性。This following example relates parent and child tables through a single-column foreign key and shows how a foreign key constraint enforces referential integrity.

使用以下 SQL 语句创建父表和子表：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);

验证数据是否已插入。您可以通过从 parent 中选择所有行来简单地完成此操作，如下所示：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 |
+----+

使用以下 SQL 语句向子表插入一行：Insert a row into the child table using the following SQL statement:

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

插入操作成功，因为父表中存在 parent_id 1。The insert operation is successful because parent_id 1 is present in the parent table.

向子表插入一个在父表中不存在的 parent_id 值的行会被拒绝并报错，如下所示：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`))

操作失败，因为指定的 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`))

此操作失败，因为子表中的记录包含被引用的 id（parent_id）值。This operation fails because the record in the child table contains the referenced id (parent_id) value.

当操作影响父表中的关键值，而子表中存在匹配的行时，结果取决于 ON UPDATE 子句的 ON UPDATE 和 ON DELETE 子句指定的参照操作。在当前子表定义中省略 ON DELETE 和 FOREIGN KEY 子句，与指定 RESTRICT 选项相同，它会拒绝对父表中具有匹配行的关键值进行的操作。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.

为了演示 ON DELETE 和 ON UPDATE 参照操作，请删除子表并重新创建它，以包含带有 CASCADE 选项的 ON UPDATE 和 ON DELETE 子子句。当在父表中删除或更新行时，CASCADE 选项会自动删除或更新子表中匹配的行。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 |
+------+-----------+

使用此处显示的 SQL 语句更新父表中的 ID，将其从 1 改为 2：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 |
+----+

验证 ON UPDATE CASCADE 参照操作更新了子表，如下所示：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 |
+------+-----------+

要演示 ON DELETE CASCADE 参照操作，请删除父表中 parent_id = 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;

由于子表中的所有记录都与 parent_id = 2 相关联，ON DELETE CASCADE 参照操作会删除子表中的所有记录，如下所示：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)

有关外键约束的更多信息，请参见 第 15.1.20.5 节，“FOREIGN KEY 约束”。For more information about foreign key constraints, see Section 15.1.20.5, “FOREIGN KEY Constraints”.