Related Documentation Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual

MySQL 8.3 Reference Manual  /  ...  /  Using Foreign Keys

5.6.6 使用外键

MySQL 支持外键,它们允许跨表引用相关数据,并且外键约束有助于保持相关数据的一致性。

外键关系涉及到一个父表,它持有初始列值,并且一个子表,它的列值引用父表的列值。外键约束是在子表上定义的。

以下示例将 parentchild 表通过单列外键关联,并展示了外键约束如何强制参照完整性。

使用以下 SQL 语句创建父表和子表:

Press CTRL+C to copy
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;

将一行插入父表,如下所示:

Press CTRL+C to copy
mysql> INSERT INTO parent (id) VALUES ROW(1);

验证数据是否插入成功。你可以简单地从 parent 表中选择所有行,如下所示:

Press CTRL+C to copy
mysql> TABLE parent; +----+ | id | +----+ | 1 | +----+

使用以下 SQL 语句将一行插入子表:

Press CTRL+C to copy
mysql> INSERT INTO child (id,parent_id) VALUES ROW(1,1);

插入操作成功,因为 parent_id 1 存在于父表中。

如果插入子表的一行具有不存在于父表中的 parent_id 值,则插入操作将被拒绝,像这样:

Press CTRL+C to copy
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 值不存在于父表中。

尝试删除父表中之前插入的行也将失败,如下所示:

Press CTRL+C to copy
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)。

当操作影响父表中的键值时,该结果取决于 ON UPDATEON DELETE 子句指定的参照操作。如果省略 ON DELETEON UPDATE 子句(如当前子表定义),则相当于指定 RESTRICT 选项,该选项拒绝影响父表中键值的操作,该键值在子表中有匹配的行。

要演示 ON DELETEON UPDATE 参照操作,删除子表并重新创建它,以包括 ON UPDATEON DELETE 子句,带有 CASCADE 选项。CASCADE 选项自动删除或更新子表中的匹配行,当删除或更新父表中的行时。

Press CTRL+C to copy
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;

使用以下语句将一些行插入子表:

Press CTRL+C to copy
mysql> INSERT INTO child (id,parent_id) VALUES ROW(1,1), ROW(2,1), ROW(3,1);

验证数据是否插入成功,如下所示:

Press CTRL+C to copy
mysql> TABLE child; +------+-----------+ | id | parent_id | +------+-----------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +------+-----------+

更新父表中的 ID,从 1 改为 2,使用以下 SQL 语句:

Press CTRL+C to copy
mysql> UPDATE parent SET id = 2 WHERE id = 1;

验证更新是否成功,选择所有行从父表,如下所示:

Press CTRL+C to copy
mysql> TABLE parent; +----+ | id | +----+ | 2 | +----+

验证 ON UPDATE CASCADE 参照操作是否更新了子表,如下所示:

Press CTRL+C to copy
mysql> TABLE child; +------+-----------+ | id | parent_id | +------+-----------+ | 1 | 2 | | 2 | 2 | | 3 | 2 | +------+-----------+

要演示 ON DELETE CASCADE 参照操作,删除父表中的记录,其中 parent_id = 2;这将删除父表中的所有记录。

Press CTRL+C to copy
mysql> DELETE FROM parent WHERE id = 2;

因为所有子表记录都与 parent_id = 2 相关联,因此 ON DELETE CASCADE 引用操作删除了子表中的所有记录,如下所示:

Press CTRL+C to copy
mysql> TABLE child; Empty set (0.00 sec)

有关外键约束的更多信息,请参阅 第 15.1.20.5 节,“外键约束”