P71 外键约束的使用

6.外键约束

6.1在create table时添加

#7.1 在create table时添加

#主表和从表：父表和子表

#①先创建主表

CREATE TABLE dept1(
dept_id INT,
dept_name VARCHAR(15)
);

#②再创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,

#表级约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id)
);

#上述操作报错，因为主表中的dept_id 上没有主键约束或唯一性约束
#③ 添加主键约束
ALTER TABLE dept1
ADD PRIMARY KEY(dept_id);

DESC dept1;

#④再创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,

#表级约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id)
);

DESC emp1;

SELECT * FROM information_schema.table_constraints
WHERE table_name = 'emp1';

#7.2演示外键效果
#添加失败，主表dept1中没有10号部门，无法添加
INSERT INTO emp1
VALUES(1001,'Tom',10);

#先给主表中添加10号部门
INSERT INTO dept1
VALUES(10,'IT');
#在主表dept1中添加了10号部门以后就能在从表中添加10号部门的员工
INSERT INTO emp1
VALUES(1001,'Tom',10);

#删除失败，应该先删除10号部门的员工，再删除10号部门
DELETE FROM dept1
WHERE dept_id=10;
#更新失败，10号部门存在员工
UPDATE dept1
SET dept_id=30
WHERE dept_id=10;

6.2在alter table时，添加外键约束

#7.3 在alter table时，添加外键约束
CREATE TABLE dept2(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);

CREATE TABLE emp2(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT
);

ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(dept_id);

SELECT * FROM information_schema.table_constraints
WHERE table_name = 'emp2';

6.3约束等级

/*
Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录

Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子
表的外键列不能为not null

No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
Restrict方式 ：同no action， 都是立即检查外键约束

Set default方式 （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置
成一个默认的值，但Innodb不能识别
*/
#演示：
#on update cascade on delete set null

CREATE TABLE dept(
did INT PRIMARY KEY, #部门编号
dname VARCHAR(50) #部门名称
);

CREATE TABLE emp(
eid INT PRIMARY KEY, #员工编号
ename VARCHAR(5),     #员工姓名
deptid INT,          #员工所在的部门
FOREIGN KEY (deptid) REFERENCES dept(did) ON UPDATE CASCADE ON DELETE SET NULL
#把修改操作设置为级联修改等级，把删除操作设置为set null等级
);

INSERT INTO dept VALUES(1001,'教学部');
INSERT INTO dept VALUES(1002, '财务部');
INSERT INTO dept VALUES(1003, '咨询部');

INSERT INTO emp VALUES(1,'张三',1001); #在添加这条记录时，要求部门表有1001部门
INSERT INTO emp VALUES(2,'李四',1001);
INSERT INTO emp VALUES(3,'王五',1002);

UPDATE dept
SET did=1004
WHERE did=1002;

DELETE FROM dept
WHERE did=1004;

SELECT * FROM dept;
SELECT * FROM emp;

#结论：对于外键约束，最好是采用ON UPDATE CASCADE ON DELETE RESTRICT 的方式

6.4删除外键约束

#7.5 删除外键约束

#一个表中可以声明有多个外键约束
USE atguigudb;
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'employees';

USE dbtest13;
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'emp1';

#删除
ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;

#再手动删除外键约束对应的普通索引
SHOW INDEX FROM emp1;

ALTER TABLE emp1
DROP INDEX fk_emp1_dept_id;