MySQL 支持外键,允许跨表交叉引用相关数据,以及外键约束,这有助于保持相关数据的一致性。
外键关系涉及包含初始列值的父表,以及包含引用父列值的列值的子表。在子表上定义了外键约束。
以下示例通过单列外键关联表,并显示外键约束如何强制执行参照完整性
parent
。
child
创建父表和子表:
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;
在父表中插入一行:
mysql> INSERT INTO parent (id) VALUES (1);
验证数据是否已插入:
mysql> SELECT * FROM parent;
+----+
| id |
+----+
| 1 |
+----+
在子表中插入一行:
mysql> INSERT INTO child (id,parent_id) VALUES (1,1);
插入操作成功,因为
parent_id
父表中存在 1。
parent_id
使用父表中不存在
的值向子表中插入一行
:
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
值在父表中不存在。
尝试从父表中删除先前插入的行:
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 UPDATE
和
ON DELETE
子句指定的引用操作FOREIGN
KEY
。省略ON DELETE
and
ON UPDATE
子句(如在当前子表定义中)与指定
RESTRICT
选项相同,它拒绝影响父表中具有匹配行的父表中的键值的操作。
要演示ON DELETE
和ON
UPDATE
参考操作,请删除子表并重新创建它以使用选项包含ON UPDATE
和
ON DELETE
子句
CASCADE
。CASCADE
当删除或更新父表中的行时,
该
选项会自动删除或更新子表中的匹配行。
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;
将以下行插入子表:
mysql> INSERT INTO child (id,parent_id) VALUES(1,1),(2,1),(3,1);
验证数据是否已插入:
mysql> SELECT * FROM child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
+------+-----------+
更新父表中的 id,将其从 1 更改为 2。
mysql> UPDATE parent SET id = 2 WHERE id = 1;
验证更新是否成功:
mysql> SELECT * FROM parent;
+----+
| id |
+----+
| 2 |
+----+
验证ON UPDATE CASCADE
引用操作是否更新了子表:
mysql> SELECT * FROM child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
+------+-----------+
为了演示ON DELTE CASCADE
引用操作,从 所在的父表中parent_id = 2
删除记录,这将删除父表中的所有记录。
mysql> DELETE FROM parent WHERE id = 2;
因为子表中的所有记录都与 关联
parent_id = 2
,ON DELETE
CASCADE
引用操作从子表中删除所有记录:
mysql> SELECT * FROM child;
Empty set (0.00 sec)
有关外键约束的更多信息,请参阅 第 13.1.18.5 节,“外键约束”。