MySQL中的外键约束

外键约束

在实际开发的项目中，一个健壮数据库中的数据一定有很好的参照完整性。例如，员工管理系统中有员工表和部门表，如果员工表的部门编号字段使用了部门编号20，而部门表中的编号20却被删除了，那么就会产生垃圾数据或错误数据。为保证数据的完整性，可以在员工表中添加外键约束。

添加外键约束

外键是数据表中的一个特殊字段，它引用另一张数据表中的一列或多列，被引用的列应该具有主键约束或唯一约束。例如在员工表emp的deptno字段上添加外键约束，引用部门表dept的主键字段deptno，如此就通过外键加强了员工表和部门表数据之间的关联。

对于两个具有关联关系的数据表来说，相关联字段中主键所在的数据表就是主表，外键所在的数据表就是从表。

在MySQL中为从表添加外键约束的语法格式如下。

ALTER TABLE 从表名 ADD CONSTRAINT [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)；

在上述语法格式中，ADD CONSTRAINT表示添加约束；外键名称是可选参数，用来指定添加的外键约束的名称；FOREIGN KEY表示外键约束；使用REFERENCES指定创建的外键引用哪个表的主键。

接下来，根据上述语法格式，为员工表emp添加外键约束，具体语句及执行结果如下。

mysql> ALTER TABLE emp ADD CONSTRAINT wjname FOREIGN KEY(deptno)  REFERENCES dept(deptno);
Query OK, 9 rows affected (0.06 sec)
Records: 9  Duplicates: 0  Warnings: 0

添加外键成功后，可以使用SHOW CREATE TABLE语句查看员工表emp的创建语句，查询语句及结果如下。

mysql> SHOW CREATE TABLE emp;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table


                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `empno` int NOT NULL,
  `ename` varchar(20) NOT NULL,
  `job` varchar(20) NOT NULL,
  `mgr` int DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` int DEFAULT NULL,
  PRIMARY KEY (`empno`),
  UNIQUE KEY `ename` (`ename`),
  KEY `wjname` (`deptno`),
  CONSTRAINT `wjname` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

在上述输出结果中，第12行表示创建了一个和外键名称同名的索引，第13行表示deptmo字段上创建了名称为wjname的外键，wjname外键引用部门表dept中的deptno字段。

在为表添加外键约束时，需要注意以下情况。

建立外键的表必须使用InnoDB引擎(默认的存储引擎)，不能是临时表，因为在MySQL中只有InnoDB引擎才允许使用外键。

定义的外键名称不能加引号，如CONSTRAINT'FK_ID'或CONSTRAINT"FKID”都是错误的。

外键所在列的数据类型必须和主表中主键对应列的数据类型相同。

需要大家注意的是外键名称是自定义的，根据表的具体结构来定义，这里的外键名称是wjname，这是为了大家更好的理解。

操作关联表

在实际开发中，需要根据实体的内容设计数据表，实体间会有各种关联关系，因此数据表之间也存在着各种关联关系。下面对数据表的关联关系、关联表添加数据、关联表删除数据进行讲解。

1.数据表的关联关系

根据数据关系，MySQL中数据表的关联关系可以分为一对一、多对一、多对多3种。这3种关联关系具体介绍如下。

(1)一对一。

一对一在实际生活中比较常见，例如人与身份证之间就是一对一的关系，一个人对应一张身份证，一张身份证只能匹配一个人。

一对一关系的两张数据表建立外键时，要分清主从关系。例如，身份证是人的附属，身份证需要人的存在才有意义。同样，在数据表的主从关系中，从表需要主表的存在才有意义。假如有身份证和人两张数据表，那么人为主表，身份证为从表，需要在身份证表中建立外键。

需要注意的是，一对一关联关系在数据库中并不常见，因为以这种方式存储的信息通常会放在一个表中。在实际开发中，一对一关联关系可以应用于如下场景。

分割具有很多列的表。

由于安全原因而隔离表的一部分。

保存临时数据，并且可以毫不费力地通过删除保存临时数据的表而删除这些数据。

(2)多对一。

多对一关联是数据表之间最常见的一种关联关系。例如员工与部门之间的关系，个部门可以有多个员工，而一个员工不能属于多个部门；也就是说部门表中的一行记录在员工表中可以有多行匹配的记录，但员工表中的一行记录在部门表中只能有一行匹配的记录。

通过之前的讲解可以知道，表之间的关系是通过外键建立的。在多对一的表关系中，应该将外键添加在“多”的一方，否则会造成数据的冗余。

3)多对多。

多对多也是数据表之间的一种关联关系。例如学生与课程之间的关系，一个学生可以选择多门课程，当然一门课程也供多个学生选择；也就是说学生表中一行记录在课程表中可以有多行匹配的记录，课程表中的一行记录在学生表中也可以有多行匹配的记录。

通常情况下，为实现多对多关联关系，需要定义一张中间表(称为连接表)。中间表会存在两个外键，分别引用课程表和学生表。

2.关联表添加数据

前面已经为员工表emp添加外键约束。此时员工表emp和部门表dept之间是多对一的关联关系。下面演示在这两个关联表中添加数据。

(1)往主表dept中插入数据。因为从表emp的外键列只能插入所引用的列(部门表的deptmo字段)中存在的值，所以如果要为两个数据表添加数据，就需要先为主表dept添加数据，插入数据的SQL语句如下。

mysql> INSERT INTO dept VALUES(50,'人力资源部');

Query OK, 1 row affected (0.01 sec)

(2)往从表emp中插入数据。在主表中添加的数据中，主键 deprno的值包含10、20、30、40和50，由于员工表cmp的外键引用部门表的主键deplto，因此在往员工表emp中添加数据时，其deptno字段的值只能是10、20、30、40和50，不能使用其他的值，具体语句如下。

mysql> INSERT INTO emp VALUES
    -> (886,'华佗','运营专员',9839,3500,200,40),
    -> (888,'曹操','人事专员',9966,3500,NULL,50);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

数据插入成功后，如果要查询人力资源部有哪些员工，可以使用链接查询完成，也可以使用子查询完成。例如使用内连接查询完成查询需求，具体SQL语句及执行结果如下。

mysql> SELECT e.*,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno AND d.dname='人力资源部';
+-------+--------+--------------+------+---------+------+--------+-----------------+
| empno | ename  | job          | mgr  | sal     | comm | deptno | dname           |
+-------+--------+--------------+------+---------+------+--------+-----------------+
|   888 | 曹操   | 人事专员     | 9966 | 3500.00 | NULL |     50 | 人力资源部      |
+-------+--------+--------------+------+---------+------+--------+-----------------+
1 row in set (0.00 sec)

从上述执行结果可以得出，人力资源部只有1名员工。需要注意的是，外键约束是为了保证数据的完整性和统一性，主表和从表中进行数据的新增、编辑、删除时需要遵循外键约束的要求，但是对数据的查询没有约束性。

3.关联表删除数据

除了为关联表添加数据，某些情况下也存在删除关联表中数据的需求。例如，因为公司组织架构调整，需要取消人力资源部，此时就需要在数据库中将人力资源部删除。下面演示删除关联表中部门表的数据。

由于员工表emp和部门表dept之间使用外键进行了关联，因此主表dept中已经被引用的值不能直接删除。如果要删除人力资源部，需要先将人力资源部中的员工删除，或者转移到其他部门，又或者不分配部门(部门编号设置为NULL)。在此选择先删除人力资源部中的员工，再删除部门表中的人力资源部。

(1)删除从表emp中属于人力资源部的员工信息，具体SQL语句及执行结果如下。

mysql> DELETE FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='人力资源部');
Query OK, 1 row affected (0.01 sec)

从上述语句的执行结果可以得出，删除语句执行成功。为验证删除的情况，可以在员工表emp中查询属于人力资源部的员工信息，具体SQL语句及执行结果如下。

mysql> SELECT e.*,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno AND d.dname='人力资源部';
Empty set (0.00 sec)

使用SELECT语句也可以直接查询：

mysql> select*from emp;
+-------+-----------+--------------+------+---------+---------+--------+
| empno | ename     | job          | mgr  | sal     | comm    | deptno |
+-------+-----------+--------------+------+---------+---------+--------+
|   886 | 华佗      | 运营专员     | 9839 | 3500.00 |  200.00 |     40 |
|   911 | 王五      | 分析员       | 9866 | 4000.00 |    NULL |     20 |
|   935 | 陈十一    | 经理         | 9839 | 3500.00 |    NULL |     10 |
|   936 | 吴九      | 销售         | 9698 | 2250.00 | 1000.00 |     30 |
|   951 | 郑十      | 销售         | 9698 | 2500.00 |    0.00 |     30 |
|   952 | 周八      | 销售         | 9698 | 2250.00 |  500.00 |     30 |
|   969 | 萧二      | 保洁         | 9698 | 2000.00 |    NULL |     30 |
|   985 | 刘一      | 董事长       | NULL | 6000.00 |    NULL |     10 |
|   991 | 赵六      | 分析员       | 9566 | 4000.00 |    NULL |     20 |
|   994 | 孙七      | 销售         | 9698 | 2500.00 |  300.00 |     30 |
+-------+-----------+--------------+------+---------+---------+--------+
10 rows in set (0.00 sec)

从执行结果来看，已经没有了人力资源部员工曹操的信息了，证明我们已经成功删除了。

这个删除是比较麻烦的，我们也可以直接使用在删除数据表的部分数据中使用的语句：

mysql> DELETE FROM emp WHERE ename='华佗';
Query OK, 1 row affected (0.00 sec)

这个相对来说简单多了。简单粗暴还好用。

(2)删除主表dept中的数据。此时从表emp中已经没有数据引用主表dept中主键值为人力资源部的记录，可以删除主表dept中部门名称为人力资源部的记录，具体SQL语句及执行结果如下。

mysql> DELETE FROM dept WHERE dname='人力资源部';
Query OK, 1 row affected (0.00 sec)

从上述语句的执行结果可以得出，删除语句执行成功。为验证删除的情况，可以对名称为人力资源部的部门信息进行查询，具体SQL语句及执行结果如下。

mysql> SELECT * FROM dept;
+--------+-----------+
| deptno | dname     |
+--------+-----------+
|     20 | 研究院    |
|     10 | 裁决室    |
|     40 | 运营部    |
|     30 | 销售部    |
+--------+-----------+
4 rows in set (0.00 sec)

可以看到人力资源部已经删除了。

运营部也没有人了，试试能不能删除运营部：

mysql> DELETE FROM dept WHERE dname='运营部';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM dept;
+--------+-----------+
| deptno | dname     |
+--------+-----------+
|     20 | 研究院    |
|     10 | 裁决室    |
|     30 | 销售部    |
+--------+-----------+
3 rows in set (0.00 sec)

从执行结果来看，运营部也成功删除了，也是简单粗暴，既然有简单的办法大家就用简单的方法就行。

删除外键约束

在实际开发中，根据业务逻辑需求，如果需要解除两个表之间的关联关系，就需要删除外键约束。删除外键约束的语法格式如下;

ALTER TABLE 外键所在表的表名（从表表名） DROP FOREIGN KEY 外建名;

接下来，将员工表emp中的外键约束删除，具体SQL语句及执行结果如下。

mysql> ALTER TABLE emp DROP FOREIGN KEY wjname;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

语法执行成功后，查看员工表emp的创建信息，查询语句及执行结果如下。

mysql> SHOW CREATE TABLE emp;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table


   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `empno` int NOT NULL,
  `ename` varchar(20) NOT NULL,
  `job` varchar(20) NOT NULL,
  `mgr` int DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` int DEFAULT NULL,
  PRIMARY KEY (`empno`),
  UNIQUE KEY `ename` (`ename`),
  KEY `wjname` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

从执行结果来看，员工表emp中的外键已经被成功删除。