MySQL 支持外键,外键允许跨表交叉引用相关数据,而外键约束有助于保持相关数据的一致性。
外键关系涉及一个父表,父表保存初始列值,以及一个子表,子表保存引用父表列值的列值。外键约束定义在子表上。
以下示例通过单列外键将 parent
和 child
表关联起来,并展示了外键约束如何强制引用完整性。
使用以下 SQL 语句创建父表和子表
Press CTRL+C to copyCREATE 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 copymysql> INSERT INTO parent (id) VALUES ROW(1);
验证数据是否已插入。您可以简单地从 parent
中选择所有行来完成此操作,如以下所示
Press CTRL+C to copymysql> TABLE parent; +----+ | id | +----+ | 1 | +----+
使用以下 SQL 语句向子表插入一行
Press CTRL+C to copymysql> INSERT INTO child (id,parent_id) VALUES ROW(1,1);
插入操作成功,因为 parent_id
1 存在于父表中。
向子表插入具有不存在于父表中的 parent_id
值的行将被拒绝,并出现错误,如以下所示
Press CTRL+C to copymysql> 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 copymysql> 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
) 值。
当操作影响父表中具有子表中匹配行的键值时,结果将取决于 FOREIGN KEY
子句的 ON UPDATE
和 ON DELETE
子句指定的引用操作。省略 ON DELETE
和 ON UPDATE
子句(如当前子表定义中)与指定 RESTRICT
选项相同,该选项会拒绝影响父表中具有匹配行的子表中的键值的操作。
为了演示 ON DELETE
和 ON UPDATE
引用操作,请删除子表并重新创建它,使其包含 ON UPDATE
和 ON DELETE
子句,并使用 CASCADE
选项。当在父表中删除或更新行时,CASCADE
选项会自动删除或更新子表中匹配的行。
Press CTRL+C to copyDROP 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 copymysql> INSERT INTO child (id,parent_id) VALUES ROW(1,1), ROW(2,1), ROW(3,1);
验证数据是否已插入,如下所示
Press CTRL+C to copymysql> TABLE child; +------+-----------+ | id | parent_id | +------+-----------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +------+-----------+
更新父表中的 ID,将其从 1 更改为 2,使用以下所示的 SQL 语句
Press CTRL+C to copymysql> UPDATE parent SET id = 2 WHERE id = 1;
验证更新是否成功,方法是从父表中选择所有行,如以下所示
Press CTRL+C to copymysql> TABLE parent; +----+ | id | +----+ | 2 | +----+
验证 ON UPDATE CASCADE
引用操作是否更新了子表,如下所示
Press CTRL+C to copymysql> TABLE child; +------+-----------+ | id | parent_id | +------+-----------+ | 1 | 2 | | 2 | 2 | | 3 | 2 | +------+-----------+
为了演示 ON DELETE CASCADE
引用操作,请从父表中删除 parent_id = 2
的记录;这将删除父表中的所有记录。
Press CTRL+C to copymysql> DELETE FROM parent WHERE id = 2;
由于子表中的所有记录都与 parent_id = 2
相关联,因此 ON DELETE CASCADE
引用操作将从子表中删除所有记录,如以下所示
Press CTRL+C to copymysql> TABLE child; Empty set (0.00 sec)
有关外键约束的更多信息,请参见 第 15.1.20.5 节,“FOREIGN KEY 约束”.