3.6.6 使用外键

MySQL 支持外键,允许跨表交叉引用相关数据,以及外键约束,这有助于保持相关数据的一致性。

外键关系涉及包含初始列值的父表,以及包含引用父列值的列值的子表。在子表上定义了外键约束。

以下示例通过单列外键关联表,并显示外键约束如何强制执行参照完整性 parentchild

创建父表和子表:

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 (1);

验证数据是否已插入:

Press CTRL+C to copy
mysql> SELECT * FROM parent; +----+ | id | +----+ | 1 | +----+

在子表中插入一行:

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

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

parent_id使用父表中不存在 的值向子表中插入一行 :

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

操作失败,因为指定的 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子句指定的引用操作FOREIGN KEY。省略ON DELETEand ON UPDATE子句(如在当前子表定义中)与指定 RESTRICT选项相同,它拒绝影响父表中具有匹配行的父表中的键值的操作。

要演示ON DELETEON UPDATE参考操作,请删除子表并重新创建它以使用选项包含ON UPDATEON DELETE子句 CASCADECASCADE当删除或更新父表中的行时, 该 选项会自动删除或更新子表中的匹配行。

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(1,1),(2,1),(3,1);

验证数据是否已插入:

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

更新父表中的 id,将其从 1 更改为 2。

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

验证更新是否成功:

Press CTRL+C to copy
mysql> SELECT * FROM parent; +----+ | id | +----+ | 2 | +----+

验证ON UPDATE CASCADE引用操作是否更新了子表:

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

为了演示ON DELTE CASCADE 引用操作,从 所在的父表中parent_id = 2删除记录,这将删除父表中的所有记录。

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

因为子表中的所有记录都与 关联 parent_id = 2ON DELETE CASCADE引用操作从子表中删除所有记录:

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

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