Mysql之外键约束

最新推荐文章于 2024-07-26 09:15:09 发布

～海绵爱宝宝～

最新推荐文章于 2024-07-26 09:15:09 发布

阅读量763

点赞数 1

分类专栏：个人总结问题文章标签： mysql

本文链接：https://blog.csdn.net/weixin_52086701/article/details/120235688

版权

个人总结同时被 2 个专栏收录

16 篇文章 0 订阅

订阅专栏

问题

5 篇文章 0 订阅

订阅专栏

外键约束Foreign Key

一.创建表的同时创建外键
- 1.创建部门（dept）和员工（emp）表，并创建外键。
- 2.把 emp 表的外键设置为级联更新和级联删除
二、删除外键约束
三、为已有的表添加外键约束

外键约束Foreign Key:外键具有保持数据完整性和一致性的机制，对业务处理有着很好的校验作用。创建外键必须满足：
一个表外键所包含的列的类型和与之发生关联的另一个表的主键列的数据类型必须相似，也就是可以相互转换类型的列，数据类型最好相同。

一.创建表的同时创建外键

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)
-- 4、更新父表的字段 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

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;
*************************** 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

二、删除外键约束

删除外键约束之前需要先查询外键约束名，可以使用 show create table 表名。删除外键约束的语法格式如下：

show create table 表名;
alter table 表名 drop foreign key 约束名;

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

命令格式如下：

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