数据库系统原理与应用教程（031）—— MySQL 的数据完整性（四）：定义外键（FOREIGN KEY）

目录

数据库系统原理与应用教程（031）—— MySQL 的数据完整性（四）：定义外键（FOREIGN KEY）一、定义外键的语法说明二、新建表同时定义外键1、定义外键2、查看外键名称3、输入数据4、验证参照完整性5、设置外键为级联更新和级联删除6、验证外键约束三、为已存在的表添加外键四、删除外键1、查询外键约束名2、删除外键

目前 MySQL 只在 InnoDB 引擎下支持外键。外键具有保持数据完整性和一致性的机制，对业务处理有着很好的校验作用。创建外键必须满足以下条件：

（1）两个表必须是 InnoDB 存储引擎，MyISAM 存储引擎不支持外键约束。

（2）一个表外键所包含的列的类型和与之发生关联的另一个表的主键列的数据类型必须相似，也就是可以相互转换类型的列，数据类型最好相同。

一、定义外键的语法说明

MySQL 定义外键的语法格式如下：

-- 定义表的同时创建外键create table 表名 (列名 类型 ... ,[CONSTRAINT 约束名] FOREIGN KEY (列名) REFERENCES 表名 (列名)[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}][ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]);/*说明：定义外键约束之后，在删除父表记录和更新父表的主键时可以设置以下操作方式：（1）cascade 方式：在父表上更新和删除记录时，子表匹配的记录也同步进行更新（级联更新）和删除（级联删除）；（2）set null 方式：在父表上更新和删除记录时，子表匹配的记录的外键设为null；（3）No action 方式：如果子表中有匹配的记录，则不允许对父表对应的主键进行更新和删除操作；（4）Restrict 方式：同 no action, 都是立即检查外键约束；（5）Set default 方式：父表上更新和删除记录时，子表将外键列设置成一个默认的值；（6）系统默认为No action 方式。*/-- 为一个已存在的表定义外键alter table 表名add constraint 约束名foreign key(列名) references 父表名(列名)[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}][ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}];

二、新建表同时定义外键

可以在创建表的同时定义外键，其语法如下：

create table 表名 (列名 类型 ... ,[CONSTRAINT 约束名] FOREIGN KEY (列名) REFERENCES 表名 (列名)[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}][ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]);

例如：

1、定义外键

/*-- 创建父表 t51，d_id 为主键create table t51(d_id int primary key,d_name char(20));-- 创建子表 t52，d_id 为外键，和父表的 d_id 对应create table t52(e_id int primary key,e_name char(20),salary int,d_id int,foreign key(d_id) references t51(d_id));*/mysql> create table t51(->d_id int primary key,->d_name char(20)-> );Query OK, 0 rows affected (0.10 sec)mysql> create table t52(->e_id int primary key,->e_name char(20),->salary int,->d_id int,->foreign key(d_id) references t51(d_id)-> );Query OK, 0 rows affected (0.06 sec)

2、查看外键名称

mysql> show create table t52\G*************************** 1. row ***************************Table: t52Create Table: CREATE TABLE `t52` (`e_id` int(11) NOT NULL,`e_name` char(20) DEFAULT NULL,`salary` int(11) DEFAULT NULL,`d_id` int(11) DEFAULT NULL,PRIMARY KEY (`e_id`),KEY `d_id` (`d_id`),CONSTRAINT `t52_ibfk_1` FOREIGN KEY (`d_id`) REFERENCES `t51` (`d_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)-- 定义外键时没有指定名称，则自动定义名称为：t52_ibfk_1

3、输入数据

/*insert into t51 values(11,'销售部'),(12,'后勤部'),(13,'生产部'),(14,'研发部');insert into t52 values(11001,'张云',4500,11),(11002,'刘涛',4800,11),(11003,'王菲',5200,11);insert into t52 values(12001,'刘静',6200,12),(12002,'张红',5100,12);insert into t52 values(13001,'周涛',7400,13),(13002,'张强',6800,13);*/mysql> insert into t51 values(11,'销售部'),(12,'后勤部'),(13,'生产部'),(14,'研发部');Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> insert into t52 values(11001,'张云',4500,11),(11002,'刘涛',4800,11),(11003,'王菲',5200,11);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> insert into t52 values(12001,'刘静',6200,12),(12002,'张红',5100,12);Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> insert into t52 values(13001,'周涛',7400,13),(13002,'张强',6800,13);Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from t51;+------+-----------+| d_id | d_name |+------+-----------+| 11 | 销售部 || 12 | 后勤部 || 13 | 生产部 || 14 | 研发部 |+------+-----------+4 rows in set (0.02 sec)mysql> select * from t52;+-------+--------+--------+------+| e_id | e_name | salary | d_id |+-------+--------+--------+------+| 11001 | 张云 | 4500 | 11 || 11002 | 刘涛 | 4800 | 11 || 11003 | 王菲 | 5200 | 11 || 12001 | 刘静 | 6200 | 12 || 12002 | 张红 | 5100 | 12 || 13001 | 周涛 | 7400 | 13 || 13002 | 张强 | 6800 | 13 |+-------+--------+--------+------+7 rows in set (0.00 sec)

4、验证参照完整性

（1）在子表中插入记录时

-- 外键为空mysql> insert into t52 values(14001,'刘云飞',5100,null);Query OK, 1 row affected (0.00 sec)-- 外键不为空，在父表中有相关记录mysql> insert into t52 values(14002,'张贝贝',5600,14);Query OK, 1 row affected (0.02 sec)mysql> select * from t52;+-------+-----------+--------+------+| e_id | e_name | salary | d_id |+-------+-----------+--------+------+| 11001 | 张云| 4500 | 11 || 11002 | 刘涛| 4800 | 11 || 11003 | 王菲| 5200 | 11 || 12001 | 刘静| 6200 | 12 || 12002 | 张红| 5100 | 12 || 13001 | 周涛| 7400 | 13 || 13002 | 张强| 6800 | 13 || 14001 | 刘云飞 | 5100 | NULL || 14002 | 张贝贝 | 5600 | 14 |+-------+-----------+--------+------+9 rows in set (0.00 sec)-- 外键不为空，在父表中无相关记录：插入失败mysql> insert into t52 values(14003,'张莎莎',5400,18);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`t52`, CONSTRAINT `t52_ibfk_1` FOREIGN KEY (`d_id`) REFERENCES `t51` (`d_id`))

（2）更新父表中的主键时

mysql> select * from t51;+------+-----------+| d_id | d_name |+------+-----------+| 11 | 销售部 || 12 | 后勤部 || 13 | 生产部 || 14 | 研发部 || 15 | 公关部 |+------+-----------+5 rows in set (0.00 sec)mysql> select * from t52;+-------+-----------+--------+------+| e_id | e_name | salary | d_id |+-------+-----------+--------+------+| 11001 | 张云| 4500 | 11 || 11002 | 刘涛| 4800 | 11 || 11003 | 王菲| 5200 | 11 || 12001 | 刘静| 6200 | 12 || 12002 | 张红| 5100 | 12 || 13001 | 周涛| 7400 | 13 || 13002 | 张强| 6800 | 13 || 14001 | 刘云飞 | 5100 | NULL || 14002 | 张贝贝 | 5600 | 14 |+-------+-----------+--------+------+9 rows in set (0.00 sec)-- 可以看到，父表中的前 4 条记录在子表中有相关记录，则主键不能更新。mysql> update t51 set d_id = 21 where d_id = 11;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydb`.`t52`, CONSTRAINT `t52_ibfk_1` FOREIGN KEY (`d_id`) REFERENCES `t51` (`d_id`))mysql> -- 最后一条记录在子表中没有相关记录，可以更新mysql> update t51 set d_id = 25 where d_id = 15;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0

（3）删除父表中的记录时

mysql> select * from t51;+------+-----------+| d_id | d_name |+------+-----------+| 11 | 销售部 || 12 | 后勤部 || 13 | 生产部 || 14 | 研发部 || 25 | 公关部 |+------+-----------+5 rows in set (0.00 sec)-- 可以看到，父表中的前 4 条记录在子表中有相关记录，删除失败mysql> delete from t51 where d_id = 11;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydb`.`t52`, CONSTRAINT `t52_ibfk_1` FOREIGN KEY (`d_id`) REFERENCES `t51` (`d_id`))mysql> -- 最后一条记录在子表中没有相关记录，可以删除mysql> delete from t51 where d_id = 25;Query OK, 1 row affected (0.00 sec)mysql> select * from t51;+------+-----------+| d_id | d_name |+------+-----------+| 11 | 销售部 || 12 | 后勤部 || 13 | 生产部 || 14 | 研发部 |+------+-----------+4 rows in set (0.00 sec)

5、设置外键为级联更新和级联删除

（1）创建表并设置外键

/*-- 创建父表 t61，d_id 为主键create table t61(d_id int primary key,d_name char(20));-- 创建子表 t62，d_id 为外键，和父表的 d_id 对应，并设置外键为级联更新和级联删除create table t62(e_id int primary key,e_name char(20),salary int,d_id int,foreign key(d_id) references t61(d_id)on delete cascadeon update cascade);*/mysql> create table t61(->d_id int primary key,->d_name char(20)-> );Query OK, 0 rows affected (0.05 sec)mysql> create table t62(->e_id int primary key,->e_name char(20),->salary int,->d_id int,->foreign key(d_id) references t61(d_id)->on delete cascade->on update cascade-> );Query OK, 0 rows affected (0.03 sec)

（2）插入数据

/*insert into t61 values(11,'销售部'),(12,'后勤部'),(13,'生产部'),(14,'研发部');insert into t62 values(11001,'张云',4500,11),(11002,'刘涛',4800,11),(11003,'王菲',5200,11);insert into t62 values(12001,'刘静',6200,12),(12002,'张红',5100,12);*/mysql> insert into t61 values(11,'销售部'),(12,'后勤部'),(13,'生产部'),(14,'研发部');Query OK, 4 rows affected (0.03 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> insert into t62 values(11001,'张云',4500,11),(11002,'刘涛',4800,11),(11003,'王菲',5200,11);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> insert into t62 values(12001,'刘静',6200,12),(12002,'张红',5100,12);Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from t61;+------+-----------+| d_id | d_name |+------+-----------+| 11 | 销售部 || 12 | 后勤部 || 13 | 生产部 || 14 | 研发部 |+------+-----------+4 rows in set (0.00 sec)mysql> select * from t62;+-------+--------+--------+------+| e_id | e_name | salary | d_id |+-------+--------+--------+------+| 11001 | 张云 | 4500 | 11 || 11002 | 刘涛 | 4800 | 11 || 11003 | 王菲 | 5200 | 11 || 12001 | 刘静 | 6200 | 12 || 12002 | 张红 | 5100 | 12 |+-------+--------+--------+------+5 rows in set (0.00 sec)

6、验证外键约束

（1）在子表中插入记录时

-- 外键为空mysql> insert into t62 values(14001,'刘云飞',5100,null);Query OK, 1 row affected (0.01 sec)-- 外键不为空，在父表中有相关记录mysql> insert into t62 values(13002,'张贝贝',5600,13);Query OK, 1 row affected (0.02 sec)mysql> select * from t62;+-------+-----------+--------+------+| e_id | e_name | salary | d_id |+-------+-----------+--------+------+| 11001 | 张云| 4500 | 11 || 11002 | 刘涛| 4800 | 11 || 11003 | 王菲| 5200 | 11 || 12001 | 刘静| 6200 | 12 || 12002 | 张红| 5100 | 12 || 13002 | 张贝贝 | 5600 | 13 || 14001 | 刘云飞 | 5100 | NULL |+-------+-----------+--------+------+7 rows in set (0.00 sec)-- 外键不为空，在父表中无相关记录：插入失败mysql> insert into t62 values(14002,'张莎莎',5400,18);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`t62`, CONSTRAINT `t62_ibfk_1` FOREIGN KEY (`d_id`) REFERENCES `t51` (`d_id`) ON DELETE CASCADE ON UPDATE CASCADE)

（2）更新父表中的主键时

mysql> select * from t61;+------+-----------+| d_id | d_name |+------+-----------+| 11 | 销售部 || 12 | 后勤部 || 13 | 生产部 || 14 | 研发部 |+------+-----------+4 rows in set (0.00 sec)mysql> select * from t62;+-------+-----------+--------+------+| e_id | e_name | salary | d_id |+-------+-----------+--------+------+| 11001 | 张云| 4500 | 11 || 11002 | 刘涛| 4800 | 11 || 11003 | 王菲| 5200 | 11 || 12001 | 刘静| 6200 | 12 || 12002 | 张红| 5100 | 12 || 13002 | 张贝贝 | 5600 | 13 || 14001 | 刘云飞 | 5100 | NULL |+-------+-----------+--------+------+7 rows in set (0.00 sec)-- 可以看到，父表中的前 3 条记录在子表中有相关记录，更新主键时会自动更新子表中对应的外键-- 把 11 部门的编号更新为 21，发现子表中对应的记录的外键也自动更新为 21mysql> update t61 set d_id = 21 where d_id = 11;Query OK, 1 row affected (0.02 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from t61;+------+-----------+| d_id | d_name |+------+-----------+| 12 | 后勤部 || 13 | 生产部 || 14 | 研发部 || 21 | 销售部 |+------+-----------+4 rows in set (0.02 sec)mysql> select * from t62;+-------+-----------+--------+------+| e_id | e_name | salary | d_id |+-------+-----------+--------+------+| 11001 | 张云| 4500 | 21 || 11002 | 刘涛| 4800 | 21 || 11003 | 王菲| 5200 | 21 || 12001 | 刘静| 6200 | 12 || 12002 | 张红| 5100 | 12 || 13002 | 张贝贝 | 5600 | 13 || 14001 | 刘云飞 | 5100 | NULL |+-------+-----------+--------+------+7 rows in set (0.00 sec)-- 最后一条记录在子表中没有相关记录，更新后对子表没有影响mysql> update t61 set d_id = 24 where d_id = 14;Query OK, 1 row affected (0.03 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from t61;+------+-----------+| d_id | d_name |+------+-----------+| 12 | 后勤部 || 13 | 生产部 || 21 | 销售部 || 24 | 研发部 |+------+-----------+4 rows in set (0.00 sec)mysql> select * from t62;+-------+-----------+--------+------+| e_id | e_name | salary | d_id |+-------+-----------+--------+------+| 11001 | 张云| 4500 | 21 || 11002 | 刘涛| 4800 | 21 || 11003 | 王菲| 5200 | 21 || 12001 | 刘静| 6200 | 12 || 12002 | 张红| 5100 | 12 || 13002 | 张贝贝 | 5600 | 13 || 14001 | 刘云飞 | 5100 | NULL |+-------+-----------+--------+------+7 rows in set (0.00 sec)

（3）删除父表中的记录时

mysql> select * from t61;+------+-----------+| d_id | d_name |+------+-----------+| 12 | 后勤部 || 13 | 生产部 || 21 | 销售部 || 24 | 研发部 |+------+-----------+4 rows in set (0.00 sec)mysql> select * from t62;+-------+-----------+--------+------+| e_id | e_name | salary | d_id |+-------+-----------+--------+------+| 11001 | 张云| 4500 | 21 || 11002 | 刘涛| 4800 | 21 || 11003 | 王菲| 5200 | 21 || 12001 | 刘静| 6200 | 12 || 12002 | 张红| 5100 | 12 || 13002 | 张贝贝 | 5600 | 13 || 14001 | 刘云飞 | 5100 | NULL |+-------+-----------+--------+------+7 rows in set (0.00 sec)-- 可以看到，父表中的前 3 条记录在子表中有相关记录，删除某个记录则会自动删除子表中的相关记录mysql> delete from t61 where d_id = 21;Query OK, 1 row affected (0.00 sec)mysql> select * from t61;+------+-----------+| d_id | d_name |+------+-----------+| 12 | 后勤部 || 13 | 生产部 || 24 | 研发部 |+------+-----------+3 rows in set (0.00 sec)mysql> select * from t62;+-------+-----------+--------+------+| e_id | e_name | salary | d_id |+-------+-----------+--------+------+| 12001 | 刘静| 6200 | 12 || 12002 | 张红| 5100 | 12 || 13002 | 张贝贝 | 5600 | 13 || 14001 | 刘云飞 | 5100 | NULL |+-------+-----------+--------+------+4 rows in set (0.00 sec)-- 最后一条记录在子表中没有相关记录，删除后对子表没有影响mysql> delete from t61 where d_id = 24;Query OK, 1 row affected (0.00 sec)mysql> select * from t61;+------+-----------+| d_id | d_name |+------+-----------+| 12 | 后勤部 || 13 | 生产部 |+------+-----------+2 rows in set (0.00 sec)mysql> select * from t62;+-------+-----------+--------+------+| e_id | e_name | salary | d_id |+-------+-----------+--------+------+| 12001 | 刘静| 6200 | 12 || 12002 | 张红| 5100 | 12 || 13002 | 张贝贝 | 5600 | 13 || 14001 | 刘云飞 | 5100 | NULL |+-------+-----------+--------+------+4 rows in set (0.00 sec)

三、为已存在的表添加外键

命令格式如下：

alter table 表名add constraint 约束名foreign key(列名) references 父表名(列名)[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}][ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}];

例如：

/*-- 创建父表 t71，d_id 为主键create table t71(d_id int primary key,d_name char(20));-- 创建子表 t72，暂时不设置外键create table t72(e_id int primary key,e_name char(20),salary int,d_id int);-- 添加外键：d_id 为外键，和父表的 d_id 对应，并设置外键为级联更新和级联删除alter table t72 add constraint fk_t72_t71 foreign key(d_id) references t61(d_id)on delete cascadeon update cascade;*/mysql> create table t71(->d_id int primary key,->d_name char(20)-> );Query OK, 0 rows affected (0.05 sec)mysql> create table t72(->e_id int primary key,->e_name char(20),->salary int,->d_id int-> );Query OK, 0 rows affected (0.07 sec)mysql> alter table t72 add constraint fk_t72_t71 foreign key(d_id) references t61(d_id)-> on delete cascade-> on update cascade;Query OK, 0 rows affected (0.56 sec)Records: 0 Duplicates: 0 Warnings: 0

四、删除外键

删除外键的语法格式如下：

-- 删除外键约束之前需要先查询外键约束名，可以使用 show create table 命令查询约束名alter table 表名 drop foreign key 外键约束名;

1、查询外键约束名

mysql> show create table t72\G*************************** 1. row ***************************Table: t72Create Table: CREATE TABLE `t72` (`e_id` int(11) NOT NULL,`e_name` char(20) DEFAULT NULL,`salary` int(11) DEFAULT NULL,`d_id` int(11) DEFAULT NULL,PRIMARY KEY (`e_id`),KEY `fk_t72_t71` (`d_id`),CONSTRAINT `fk_t72_t71` FOREIGN KEY (`d_id`) REFERENCES `t61` (`d_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

2、删除外键

mysql> alter table t72 drop foreign key fk_t72_t71;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table t72\G*************************** 1. row ***************************Table: t72Create Table: CREATE TABLE `t72` (`e_id` int(11) NOT NULL,`e_name` char(20) DEFAULT NULL,`salary` int(11) DEFAULT NULL,`d_id` int(11) DEFAULT NULL,PRIMARY KEY (`e_id`),KEY `fk_t72_t71` (`d_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)