MySQL讲义第12讲——完整性约束之外键（FOREIGN KEY）约束

最新推荐文章于 2024-04-19 15:23:29 发布

睿思达DBA_WGX

最新推荐文章于 2024-04-19 15:23:29 发布

阅读量2.5k

点赞数 4

分类专栏： MySQL基本操作 MySQL讲义文章标签：数据库 mysql 完整性约束

本文链接：https://blog.csdn.net/weixin_44377973/article/details/108824976

版权

MySQL基本操作同时被 2 个专栏收录

169 篇文章 96 订阅

订阅专栏

MySQL讲义

57 篇文章 27 订阅

订阅专栏

MySQL讲义第12讲——完整性约束之外键（FOREIGN KEY）约束

文章目录

MySQL讲义第12讲——完整性约束之外键（FOREIGN KEY）约束

外键（FOREIGN KEY）具有保持数据完整性和一致性的机制，目前 MySQL 只在 InnoDB 引擎下支持外键。外键具有保持数据完整性和一致性的机制，对业务处理有着很好的校验作用。创建外键必须满足以下条件：
（1）两个表必须是 InnoDB 存储引擎，MyISAM 存储引擎暂时不支持外键约束。
（2）一个表外键所包含的列的类型和与之发生关联的另一个表的主键列的数据类型必须相似，也就是可以相互转换类型的列，数据类型最好相同。

一、创建表的同时定义外键

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

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 方式。

例子：

1、创建部门（dept）和员工（emp）表，并创建外键。

（1）创建表

create table dept (
    id int primary key auto_increment,
    name char(20)
);

create table emp (
    id int primary key auto_increment,
    name char(20),
    salary decimal(8,2),
    dept_id int,
    foreign key(dept_id) references dept(id)
);

（2）插入数据

mysql> select * from dept;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 人事部    |
|  2 | 销售部    |
|  3 | 技术部    |
|  4 | 财务部    |
+----+-----------+
4 rows in set (0.00 sec)

mysql> select * from emp;
+----+-----------+---------+---------+
| id | name      | salary  | dept_id |
+----+-----------+---------+---------+
|  1 | 张鹏      | 4500.00 |       1 |
|  2 | 王晶      | 5700.00 |       1 |
|  3 | 刘云      | 4900.00 |       2 |
|  4 | 王晓刚    | 5200.00 |       2 |
|  5 | 刘大鹏    | 4200.00 |       2 |
|  6 | 王军军    | 5600.00 |       3 |
+----+-----------+---------+---------+
6 rows in set (0.00 sec)

（3）验证外键约束

在删除父表记录和更新父表的主键时，子表的操作方式默认为No action 方式。

-- 1、删除 dept 表（父表）：删除失败
mysql> drop table dept;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
-- 2、删除 dept 表（父表）中的 1号部门（人事部）：子表中有相关记录，删除失败
mysql> delete from dept where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`wgx`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))
-- 3、删除 dept 表（父表）中的 4号部门（财务部）：由于子表中没有相关记录，成功删除
mysql> delete from dept where id=4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from dept;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 人事部    |
|  2 | 销售部    |
|  3 | 技术部    |
+----+-----------+
3 rows in set (0.00 sec)
-- 4、重新插入财务部的信息
mysql> insert into dept(id,name)  values(4,'财务部');
Query OK, 1 row affected (0.02 sec)

mysql> select * from dept;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 人事部    |
|  2 | 销售部    |
|  3 | 技术部    |
|  4 | 财务部    |
+----+-----------+
4 rows in set (0.00 sec)
-- 5、更新父表的字段 id，更新人事部的 id 失败，因为子表中存在相关记录
mysql> update dept set id=11 where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`wgx`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))
-- 6、更新父表的字段 id，更新财务部的 id 成功，因为子表中不存在相关记录
mysql> update dept set id=44 where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dept;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 人事部    |
|  2 | 销售部    |
|  3 | 技术部    |
| 44 | 财务部    |
+----+-----------+
4 rows in set (0.00 sec)

2、把 emp 表的外键设置为级联更新和级联删除

不提供对外键的直接修改，可以先删除外键，然后再重新创建。

（1）查看外键约束的名称

mysql> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) DEFAULT NULL,
  `salary` decimal(8,2) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dept_id` (`dept_id`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

-- 外键约束的名称为：emp_ibfk_1

（2）删除外键约束

mysql> alter table emp drop foreign key emp_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

（3）重新添加外键约束

mysql> alter table emp 
    -> add constraint fk_emp_dept_id foreign key(dept_id) 
    -> references dept(id)
    -> on update cascade
    -> on delete cascade;
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) DEFAULT NULL,
  `salary` decimal(8,2) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept_id` (`dept_id`),
  CONSTRAINT `fk_emp_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

（4）测试外键约束

-- 1、在dept表中把人事部的编号修改为11，销售部的编号修改22，技术部的编号修改为33
-- 可以看到，子表中对应的部门编号自动被修改
mysql> update dept set id=11 where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update dept set id=22 where id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update dept set id=33 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dept;
+----+-----------+
| id | name      |
+----+-----------+
| 11 | 人事部    |
| 22 | 销售部    |
| 33 | 技术部    |
| 44 | 财务部    |
+----+-----------+
4 rows in set (0.00 sec)

mysql> select * from emp;
+----+-----------+---------+---------+
| id | name      | salary  | dept_id |
+----+-----------+---------+---------+
|  1 | 张鹏      | 4500.00 |      11 |
|  2 | 王晶      | 5700.00 |      11 |
|  3 | 刘云      | 4900.00 |      22 |
|  4 | 王晓刚    | 5200.00 |      22 |
|  5 | 刘大鹏    | 4200.00 |      22 |
|  6 | 王军军    | 5600.00 |      33 |
+----+-----------+---------+---------+
6 rows in set (0.01 sec)

-- 2、在dept表中删除技术部的信息
-- 可以看到，技术部的员工自动被删除
mysql> delete from dept where id=33;
Query OK, 1 row affected (0.01 sec)

mysql> select * from dept;
+----+-----------+
| id | name      |
+----+-----------+
| 11 | 人事部    |
| 22 | 销售部    |
| 44 | 财务部    |
+----+-----------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+----+-----------+---------+---------+
| id | name      | salary  | dept_id |
+----+-----------+---------+---------+
|  1 | 张鹏      | 4500.00 |      11 |
|  2 | 王晶      | 5700.00 |      11 |
|  3 | 刘云      | 4900.00 |      22 |
|  4 | 王晓刚    | 5200.00 |      22 |
|  5 | 刘大鹏    | 4200.00 |      22 |
+----+-----------+---------+---------+
5 rows in set (0.00 sec)

二、删除外键约束

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

alter table 表名 drop foreign key 约束名;

删除外键约束之前需要先查询外键约束名，可以使用 show create table 表名：

-- 查看外键约束名
mysql> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) DEFAULT NULL,
  `salary` decimal(8,2) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept_id` (`dept_id`),
  CONSTRAINT `fk_emp_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
-- 创建外键约束会自动创建一个索引
-- 查看外键约束对应的索引
mysql> show index from emp;
+-------+------------+----------------+--------------+-------------+-----------
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation 
+-------+------------+----------------+--------------+-------------+-----------
| emp   |          0 | PRIMARY        |            1 | id          | A         
| emp   |          1 | fk_emp_dept_id |            1 | dept_id     | A         
+-------+------------+----------------+--------------+-------------+-----------
2 rows in set (0.00 sec)

举例：删除 emp 表的外键约束

mysql> alter table emp drop foreign key fk_emp_dept_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) DEFAULT NULL,
  `salary` decimal(8,2) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept_id` (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
-- 删除约束不会自动删除约束对应的索引
mysql> show index from emp;
+-------+------------+----------------+--------------+-------------+-----------
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation 
+-------+------------+----------------+--------------+-------------+-----------
| emp   |          0 | PRIMARY        |            1 | id          | A         
| emp   |          1 | fk_emp_dept_id |            1 | dept_id     | A         
+-------+------------+----------------+--------------+-------------+-----------
2 rows in set (0.00 sec)
-- 手工删除外键约束对应的索引
mysql> alter table emp drop index fk_emp_dept_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

三、为已有的表添加外键约束

命令格式如下：

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}]
;

举例：为 emp 表添加外键约束，名称为 fk_emp_dept_id，on delete 设置为 set null，on update 设置为 cascade。

mysql> alter table emp 
       add constraint fk_emp_dept_id 
       foreign key(dept_id) 
       references dept(id)
       on delete set null on update cascade;
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0