-- 1.6 外键约束
-- 员工表
CREATE TABLE employee(
id INT PRIMARY KEY,
empName VARCHAR(20),
deptName VARCHAR(20) -- 部门名称
)
INSERT INTO employee VALUES(1,'张三','软件开发部');
INSERT INTO employee VALUES(2,'李四','软件开发部');
INSERT INTO employee VALUES(3,'王五','应用维护部');
SELECT * FROM employee;
-- 添加员工,部门名称的数据冗余高
INSERT INTO employee VALUES(4,'陈六','软件开发部');
-- 解决数据冗余高的问题:给冗余的字段放到一张独立表中
-- 独立设计一张部门表
CREATE TABLE dept(
id INT PRIMARY KEY,
deptName VARCHAR(20)
)
DROP TABLE employee;
-- 修改员工表
CREATE TABLE employee(
id INT PRIMARY KEY,
empName VARCHAR(20),
deptId INT,-- 把部门名称改为部门ID
-- 声明一个外键约束
CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE -- ON CASCADE UPDATE :级联修改
-- 外键名称 外键 参考表(参考字段)
)
INSERT INTO dept(id,deptName) VALUES(1,'软件开发部');
INSERT INTO dept(id,deptName) VALUES(2,'应用维护部');
INSERT INTO dept(id,deptName) VALUES(3,'秘书部');
INSERT INTO employee VALUES(1,'张三',1);
INSERT INTO employee VALUES(2,'李四',1);
INSERT INTO employee VALUES(3,'王五',2);
INSERT INTO employee VALUES(4,'陈六',3);
-- 问题: 该记录业务上不合法,员工插入了一个不存在的部门数据
INSERT INTO employee VALUES(5,'陈六',4); -- 违反外键约束: Cannot add or update a child row: a foreign key constraint fails (`day16`.`employee`, CONSTRAINT `emlyee_dept_fk` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))
-- 1)当有了外键约束,添加数据的顺序: 先添加主表,再添加副表数据
-- 2)当有了外键约束,修改数据的顺序: 先修改副表,再修改主表数据
-- 3)当有了外键约束,删除数据的顺序: 先删除副表,再删除主表数据
-- 修改部门(不能直接修改主表)
UPDATE dept SET id=4 WHERE id=3;
-- 先修改员工表
UPDATE employee SET deptId=2 WHERE id=4;
-- 删除部门
DELETE FROM dept WHERE id=2;
-- 先删除员工表
DELETE FROM employee WHERE deptId=2;
SELECT * FROM dept;
SELECT * FROM employee;