mysql外键的三种模式

外键的优点和缺点：
优点：数据保持一致性。
缺点：会影响性能，数据库为了保持数据的完整性会降低性能。

第一种模式：RESTRICT

数据准备：

*创建表结构：*
create table dept(
    deptno int primary key comment "部门编号",
    name varchar(20) comment "部门名称",
    loc varchar(40) comment "所在城市"
) charset=utf8;

create table employee(
  no int comment "员工编号",
  name varchar(20) comment "姓名",
  job varchar(40) comment "职位",
  mgr int comment "上司id",
  hiredate date comment "雇佣时间",
  sal double(10, 2) comment "工资",
  comm double(10, 2) comment "奖金",
  deptno int comment "部分id",
	CONSTRAINT `fkdd` FOREIGN KEY(`deptno`) REFERENCES dept(`deptno`) on delete RESTRICT on update RESTRICT
) charset=utf8;

插入数据：
insert into dept values
(10, "accounting", "new_york"),
(20, "research", "dalas"),
(30, "sales", "beijing"),
(40, "financial", "shanghai");

insert into employee values
  (7369, "smith", "clerk", 7902, "1981-03-12", 800.00, NULL, 20),
  (7499, "allen", "salesman", 7698, "1982-03-12", 1600.00, 300.00, 30),
  (7521, "ward", "salesman", 7698, "1983-03-12", 1250.00, 500.00, 30),
  (7566, "jones", "manager", 7839, "1981-03-12", 2975.00, NULL, 20),
  (7654, "martin", "salesman", 7698, "1981-03-12", 1250.00, 1400.00, 30),
  (7698, "blake", "manager", 7839, "1981-03-12", 2850.00, NULL, 30),
  (7782, "clark", "manager", 7839, "1985-03-12", 2450.00, NULL, 10),
  (7788, "scott", "analyst", 7566, "1981-03-12", 3000.00, NULL, 20),
  (7839, "king", "president", NULL, "1981-03-12", 5000.00, NULL, 10),
  (7844, "turner", "salesman", 7698, "1989-03-12", 1500.00, 0.00, 30),
  (7876, "adams", "clerk", 7788, "1998-03-12", 1100.00, NULL, 20),
  (7900, "james", "clerk", 7698, "1997-03-12", 950.00, NULL, 30),
  (7902, "ford", "analyst", 7566, "2000-01-01", 3000.00, NULL, 20),
  (7934, "miller", "clerk", 7782, "1981-03-12", 1300.00, NULL, 10);

然后删除dept表deptno值为10的记录：

delete from dept where deptno=10;

从上图发现：
RESTRICT是严格模式，不允许删除和修改的。

第二种模式：SET NULL

数据准备：

create table dept(
    deptno int primary key comment "部门编号",
    name varchar(20) comment "部门名称",
    loc varchar(40) comment "所在城市"
) charset=utf8;

create table employee(
  no int comment "员工编号",
  name varchar(20) comment "姓名",
  job varchar(40) comment "职位",
  mgr int comment "上司id",
  hiredate date comment "雇佣时间",
  sal double(10, 2) comment "工资",
  comm double(10, 2) comment "奖金",
  deptno int comment "部分id",
	CONSTRAINT `fkdd` FOREIGN KEY(`deptno`) REFERENCES dept(`deptno`) on delete SET NULL on update SET NULL
) charset=utf8;

插入数据：
insert into dept values
(10, "accounting", "new_york"),
(20, "research", "dalas"),
(30, "sales", "beijing"),
(40, "financial", "shanghai");

insert into employee values
  (7369, "smith", "clerk", 7902, "1981-03-12", 800.00, NULL, 20),
  (7499, "allen", "salesman", 7698, "1982-03-12", 1600.00, 300.00, 30),
  (7521, "ward", "salesman", 7698, "1983-03-12", 1250.00, 500.00, 30),
  (7566, "jones", "manager", 7839, "1981-03-12", 2975.00, NULL, 20),
  (7654, "martin", "salesman", 7698, "1981-03-12", 1250.00, 1400.00, 30),
  (7698, "blake", "manager", 7839, "1981-03-12", 2850.00, NULL, 30),
  (7782, "clark", "manager", 7839, "1985-03-12", 2450.00, NULL, 10),
  (7788, "scott", "analyst", 7566, "1981-03-12", 3000.00, NULL, 20),
  (7839, "king", "president", NULL, "1981-03-12", 5000.00, NULL, 10),
  (7844, "turner", "salesman", 7698, "1989-03-12", 1500.00, 0.00, 30),
  (7876, "adams", "clerk", 7788, "1998-03-12", 1100.00, NULL, 20),
  (7900, "james", "clerk", 7698, "1997-03-12", 950.00, NULL, 30),
  (7902, "ford", "analyst", 7566, "2000-01-01", 3000.00, NULL, 20),
  (7934, "miller", "clerk", 7782, "1981-03-12", 1300.00, NULL, 10);

删除dept表中deptno值为10的记录

delete from dept where deptno=10;

删除前的数据：

从上图中发现：
dept中deptno的值为10的已经被成功删除；employee表中的deptno原来为10的值，现在变成NULL值。返回的记录数还是14条，说明，SET NULL模式只会影响外键字段的值。

第三种模式：CASCADE

数据准备：

create table dept(
    deptno int primary key comment "部门编号",
    name varchar(20) comment "部门名称",
    loc varchar(40) comment "所在城市"
) charset=utf8;

create table employee(
  no int comment "员工编号",
  name varchar(20) comment "姓名",
  job varchar(40) comment "职位",
  mgr int comment "上司id",
  hiredate date comment "雇佣时间",
  sal double(10, 2) comment "工资",
  comm double(10, 2) comment "奖金",
  deptno int comment "部分id",
	CONSTRAINT `fkdd` FOREIGN KEY(`deptno`) REFERENCES dept(`deptno`) on delete CASCADE on update CASCADE
) charset=utf8;

插入数据：
insert into dept values
(10, "accounting", "new_york"),
(20, "research", "dalas"),
(30, "sales", "beijing"),
(40, "financial", "shanghai");

insert into employee values
  (7369, "smith", "clerk", 7902, "1981-03-12", 800.00, NULL, 20),
  (7499, "allen", "salesman", 7698, "1982-03-12", 1600.00, 300.00, 30),
  (7521, "ward", "salesman", 7698, "1983-03-12", 1250.00, 500.00, 30),
  (7566, "jones", "manager", 7839, "1981-03-12", 2975.00, NULL, 20),
  (7654, "martin", "salesman", 7698, "1981-03-12", 1250.00, 1400.00, 30),
  (7698, "blake", "manager", 7839, "1981-03-12", 2850.00, NULL, 30),
  (7782, "clark", "manager", 7839, "1985-03-12", 2450.00, NULL, 10),
  (7788, "scott", "analyst", 7566, "1981-03-12", 3000.00, NULL, 20),
  (7839, "king", "president", NULL, "1981-03-12", 5000.00, NULL, 10),
  (7844, "turner", "salesman", 7698, "1989-03-12", 1500.00, 0.00, 30),
  (7876, "adams", "clerk", 7788, "1998-03-12", 1100.00, NULL, 20),
  (7900, "james", "clerk", 7698, "1997-03-12", 950.00, NULL, 30),
  (7902, "ford", "analyst", 7566, "2000-01-01", 3000.00, NULL, 20),
  (7934, "miller", "clerk", 7782, "1981-03-12", 1300.00, NULL, 10);

删除dept表中deptno值为10的记录

delete from dept where deptno=10;

从上图中发现：
dept中deptno的值为10的已经被成功删除；employee表中的deptno值为10的记录也被删除，说明，CASCADE模式是层级删除，只要删除父表中的外键值，子表中的外键值所在的记录也会一并删除。