MySQL约束(下) 外键约束

首先 我们要知道 什么叫外键

数据库表之间是可以建立关联关系的
比如 我们有一个部门表和一个员工表
每个员工都会有属于自己的部门

这个时候我们就可以用外键

我们就来模拟这种情况

先来建一个部门表 参考代码如下

CREATE TABLE IF NOT EXISTS `department`(
   `id` int primary key auto_increment,
   `name` VARCHAR(200) not null unique
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

这样我们的一张部门表就建好了

然后我们加四个部门

INSERT INTO `department`(`name`)
VALUES("开发部");
INSERT INTO `department`(`name`)
VALUES("技术部");
INSERT INTO `department`(`name`)
VALUES("市场部");
INSERT INTO `department`(`name`)
VALUES("人事部");

然后我们来创建一个员工表

CREATE TABLE IF NOT EXISTS `staff`(
   `id` int primary key auto_increment,
   `name` VARCHAR(200) not null unique,
   `age` int(65) check (age > 0 and age < 120),
   `status` VARCHAR(200) default "1",
   `departmentid` int(200)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

运行之后我们的员工表就出来了

大家应该注意到了 我们这里有一个 departmentid 这个就是员工表的外键 同时也要对应着 部门表的主键id

当然 就这样创建出来他们之间是没有任何关联的 其中就有两种情况

第一种是 你添加一个用户 给他的部门外键根本就不存在

另一种就是 你创建了一个技术部 这个技术部的主键对应了几个员工的外键 结果你直接给技术部这条数据删了
那么和技术部关联的员工数据就有问题了 所以我们要给他加一个外键约束

然后我们给他们创建外键

alter table staff add constraint employee_foreignkey foreign key (departmentid) references department(id)

然后我们的外键就已经创建

创建外键的基本语法就是

alter table 需要保存外键的表 add constraint 外键名 foreign key (外键在表中的字段名) references 外键需要关联的表(关联的字段)

然后我们将员工表加一条数据

INSERT INTO `staff`(`name`,`age`,`status`,`departmentid`)
VALUES('赵敏',20,"1",1);

运行之后去看staff表

没有任何问题 他的departmentid为1 关联的开发部

那如果我这里创建一个没有的部门外键呢
编写代码如下

INSERT INTO `staff`(`name`,`age`,`status`,`departmentid`)
VALUES('张无忌',20,"1",5);

运行之后就会报错 [Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (test.staff, CONSTRAINT employee_foreignkey FOREIGN KEY (departmentid) REFERENCES department (id))
就是在告诉你 这个外键是不存在的 首先
外键的作用第一个就是 你存的数据必须关联外键表中的一条数据 如果系统找不到 就会直接报错

其次就是我们来看删除

delete FROM `department` WHERE id like 2;

这里我们删了部门表 主键id为2的数据

然后我们再来这个有外键关联的 1 开发部

delete FROM `department` WHERE id like 1;

此时我们控制台就会报错
[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (test.staff, CONSTRAINT employee_foreignkey FOREIGN KEY (departmentid) REFERENCES department (id))

说明被外键绑定的数据也是不能删除的

不过你把用户表中关联他的数据删了 那么这条数据就可以删除了 只要没有数据用外键关联他 他就可以删

当然 外键也是可以返回的 我们可以删除外键
语法就是 alter table 表名 drop foreign key 外键名

alter table staff drop foreign key employee_foreignkey

执行这段代码就能取消我们刚才创建的外键