MySQL的外键约束（FOREIGN KEY），有点小复杂

一、什么是参照完整性

在学习外键之前，我们必须先搞懂一个概念，什么是“参照完整性”。

参照的关系中的属性值必须能够在被参照关系找到或者取空值，否则不符合数据库的语义。

在实际操作时如更新、删除、插入一个表中的数据，通过参照引用相互关联的另一个表中的数据，来检查对表的数据操作是否正确，不正确则拒绝操作。

举个例子，有一张员工表，字段为工号、姓名、所属部门。 还有一张部门表，字段为部门编号，部门名称，部门位置。

员工表demo_employee如下

部门demo_dept表如下

每一个员工都应该有一个从属部门，并且这个部门一定在部门表中有数据存在。或者此员工没有任何部门为NULL，例如员工新入职还未分配所属部门的情况。

如果某个员工尹洪亮，数据记录为从属于极光轻学的建筑业务部，但是公司根本就没有这个部门，这就代表违反了参照完整性，相反就是遵从了参照完整性。

二、MySQL的外键

1. MySQL 外键约束（FOREIGN KEY）用来在两个表的数据之间建立链接，一个表可以有一个或多个外键。

例如员工表有姓名、年龄、性别、所属部门、籍贯几个列，那么可以只有所属部门这一个列是外键，也可以所属部门和籍贯两个列都是外键。

2. 一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。
   例如员工表和部门表，员工表的外键，必须依赖于部门表的主键，可以表述为如下关系。
   demo_employee(dept_id)外键->依赖->demo_dept(dept_id)主键
3. 外键是表的一个字段，不是本表的主键，但对应另一个表的主键。定义外键后，不允许删除另一个表中具有关联关系的行。
   例如某个部门有10名员工，如果要没有把员工调整到其他部门或者遣散就直接撤销部门是不允许的。
4. 外键的主要作用是保持数据的一致性和完整性。、

三、主表与从表

1. 主表也叫做父表：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。
2. 从表也叫做子表：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。

例如，员工表和部门表的关系， 员工表依赖于部门表，所以部门表就是主表，员工表是从表，可以理解为1个部门下有多个员工。

四、如何选取外键约束字段

定义一个外键时，需要遵守下列规则：

1. 父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
   如下一张员工表，有一个所属主管列，而主管也是一名员工，所以自己要参照自己的表，这就是自参照完整性，也叫表内外键。

2. 必须为父表定义主键。
3. 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
4. 在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或候选键。
5. 外键中列的数目必须和父表的主键中列的数目相同。
6. 外键中列的数据类型必须和父表主键中对应列的数据类型相同。

五、如何设置外键

5.1 在创建表时设置外键约束

在数据表中创建外键使用 FOREIGN KEY 关键字，具体的语法如下。

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

其中：

<外键名>为定义的外键约束的名称，一个表中不能有相同名称的外键；

字段名表示子表需要添加外健约束的字段列；

主表名即被子表外键所依赖的表的名称；

主键列表示主表中定义的主键列或者列组合。

示例 1，为了展现表与表之间的外键关系，本例在 demo_db 数据库中创建一个部门表 demo_dept，表结构如下表所示。

mysql> CREATE TABLE demo_dept
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) NOT NULL,
    -> location VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.37 sec)

创建数据表 demo_employee，并在表 demo_employee 上创建外键约束，让它的键 dept_id作为外键关联到表 demo_dept 的主键 id上，编写 SQL 语句和执行结果如下所示。

mysql> CREATE TABLE demo_employee
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> dept_id INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_dept
    -> FOREIGN KEY(dept_id) REFERENCES demo_dept(id)
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC demo_employee;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| dept_id| int(11)     | YES  | MUL | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (1.33 sec)

以上语句执行成功之后，在表 demo_employee 上添加了名称为 fk_emp_dept 的外键约束，外键名称为 dept_id，其依赖于主表 demo_dept 的主键 id。

墙裂注意：子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配，如果类型不一样，则创建子表时会出现错误“ERROR 1005(HY000)：Can't create table'database.tablename'(errno：150)”。

5.2 在修改表时添加外键约束

在修改数据表时添加外键约束的语法规则为：

ALTER TABLE <数据表名> ADD CONSTRAINT <索引名> FOREIGN KEY (<列名>)  REFERENCES <主表名>(<列名>);

【示例2】修改数据表tb_employee，将字段dept_id设置为外键，与数据表demo_dept的主键id进行关联，输入的SQL语句和运行结果如下所示。

mysql> ALTER TABLE tb_employee
    -> ADD CONSTRAINT fk_demo_dept
    -> FOREIGN KEY(dept_id)
    -> REFERENCES demo_dept(id);
Query OK, 0 rows affected (1.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb_employee\G
*************************** 1. row ***************************
       Table: tb_employee
Create Table: CREATE TABLE `tb_employee` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_demo_dept` (`dept_id`),
  CONSTRAINT `fk_demo_dept` FOREIGN KEY (`dept_id`) REFERENCES `demo_dept` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.12 sec)

5.3 删除外键约束

对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主表和从表间的关联关系，MySQL 中删除外键的语法格式如下：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

示例 3，删除数据表 tb_employee 中的外键约束 fk_demo_dept，编写 SQL 语句和执行结果如下所示。

mysql> ALTER TABLE tb_employee
    -> DROP FOREIGN KEY fk_demo_dept;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb_employee\G
*************************** 1. row ***************************
       Table: tb_employee
Create Table: CREATE TABLE `tb_employee` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_demo_dept` (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

可以看到，tb_employee 中已经不存在 FOREIGN KEY，原有的名称为 fk_emp_dept 的外键约束删除成功。

MySQL是日常工作中使用最对的数据库之一，所以必须要要较为深入和全面的掌握，对于高阶人员还要掌握分布式事务、各种数据库锁、传播机制等。