mysql 逻辑外键的设置

本博客转载自     https://www.jianshu.com/p/ab58fa7e8146

本博客字体标红部分为设置外键的SQL语句

1.假如我们现在有个这样的需求，我们公司有以下几个部门，“研发部”，“产品部”，“运维部”，当有新员工进入的时候，需要只能加入以下三个部门的某个部门，没有列表所示的部门将不能加入，这个时候，我们可以用到外键

我们来创建两个表，create table if NOT EXISTS dept(id TINYINT AUTO_INCREMENT PRIMARY KEY,name varchar(20));这是个部门表，

create table if NOT EXISTS persion(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

userName varchar(20) NOT null UNIQUE ,

depid TINYINT

);这是员工表，接下来我们添加外键，    ALTER TABLE PERSION ADD CONSTRAINT PER_FK_DEPT FOREIGN KEY(depid) REFERENCES DEPT(id)    --添加外键  PER_FK_DEPT;PER_FK_DEPT使我们的外键名字，这个时候我们的外键就添加成功了，在我们没有指定的情况下就是默认的NO ACTION    情况，下面，我们来试一下，首先往部门表里面插入几个部门    

INSERT INTO dept(NAME) VALUES("研发部");

INSERT INTO dept(NAME) VALUES("产品部");

INSERT INTO dept(NAME) VALUES("运维部");

OK ,三个部门添加成功了，SELECT *FROM DEPT;看一下数据

 

再往第二个表里面插入三条数据

INSERT INTO persion(userName,depid) VALUES('孙1',1);

INSERT INTO persion(userName,depid) VALUES('王2',2);

mysql> INSERT INTO persion(userName,depid) VALUES('张三',3);

OK，这个时候，我们插入了三条数据，他们的depid分别为1，2，3，也就是对应的dept表里面的三个部门，这个时候，我们再插入一条数据

    INSERT INTO persion(userName,depid) VALUES('赵4',4);

这个时候会看到控制台报错，

 

原因是员工表里面的deptid和部门表里面的id是关联的，他的外键和部门必须对应，这个时候插入一个员工到不存在的部门是不可以的，这个时候我们删除一个部门看一下，

 

又报错了，这是因为外键的原因，如果想删除某个部门，必须删除这个部门下的员工才可以将部门删除，接下来，我们先从员工表里面把某个员工删除，再来删除某个部门试一下， DELETE FROM persion where depid =1;

DELETE FROM dept where id =1;

 

OK,这个时候，就可以看到部门删除成功了，对应的员工也删除了，那么，我们不想删除员工，删除部门的时候，直接将对应的员工删除怎么做呢，就是下面说的这种情况

2.假如这个时候我们想删除某个部门，取消某个部门，比如我们想把运维部去掉，和到研发部门里面，而这个时候有两种情况，一是，加入删除了这个部门，而这个部门下所有的人都应该删除，第二种情况就是，删除了这个部门，而这个部门下的员工不删除，只是无家可归，我们可以给个null，这个时候，我们也可以用外键

如果我们想删除部门的时候，将员工也删除掉怎么做呢

首先先将我们刚才创建的外键删除了，重新来设置一下外键

ALTER TABLE persion DROP FOREIGN KEY PER_FK_DEPT;     --  删除外键PER_FK_DEPT

 

,OK,我们看一下是否删除成功，看一下表结构

SHOW CREATE TABLE PERSION \G;

 

可以看到，虽然外键删除成功了，三十索引还在，接下来把索引也删除了，

    ALTER TABLE persion DROP INDEX PER_FK_DEPT;       --删除索引 PER_FK_DEPT

然后看一下表结构，

DESC persion;

 

可以发现，索引删除了，

接下来我们创建一个外键

ALTER TABLE persion ADD CONSTRAINT PER_FK_DEPT FOREIGN KEY(depid) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE;   --创建外键，使主表删除信息的同时，从表相关联的也删除，主表修改，从表关联的外键也修改

这个时候，我们就创建成了一个级联的外键，这个时候，当我们删除部门表里面的某个部门的时候，员工表里面，这个部门对应的所有员工都会跟着删除,当我们更新部门表里的部门的时候，员工表也会跟着删除，试一下，先看一下，两个表里面的数据

 

可以看到数据是一一对应的，这个时候我想把所有的部门编号都加10，而这个时候，我们的员工表里面的员工对用的部门ID也会跟着加10，那么会不会做到呢

update dept set id=id+10;

这个时候，我们再来看一下两个标的数据，

 

可以看到，当我们更改部门表的时候，员工表也跟着删除，这个时候，我想删除产品部这个部门，那么员工表里面的员工会跟着删除吗？

DELETE FROM dept where id =12;

再看一下表里的数据

 

可以看到，两个表里面的数据都删除了。

3.上面那种情况是删除主表，字表也跟着删除，现在我想删除某个部门的时候，里面的人不删除，只是让他无家可归，给个null，怎么做呢

首先删除我们刚才创建的外键

ALTER table persion DROP FOREIGN KEY PER_FK_DEPT;    --删除外键  PER_FK_DEPT

ALTER table persion DROP INDEX PER_FK_DEPT;  --删除索引   PER_FK_DEPT

然后我们再创建一个外键

ALTER table persion ADD CONSTRAINT PER_FK_DEPT FOREIGN KEY(depid) REFERENCES dept(id) on delete SET NULL on update SET NULL;  --创建外键，使主表中的信息删除或者更新，从表中外键的字段值变为null。

OK，这时候，我们再更新或者删除主表中的数据试一下，

DELETE FROM dept where id = 13;

OK ,我们删除成功了，这个时候，我们再来看一下两个标的数据

 

这个时候，可以看到，主表的数据已经完全删光了，而从表对应的人，只是给了个null，人还在，只是没有部门了，达到了我们的要求

到这里，我们的物理外键讲完了，一般开发中，我们很少用到物理外键，不让数据库去部或错误，我们认为去捕获，所以一般我们用到逻辑外键。