mysql级联删除
场景:
员工表
id
:员工idleader_id
:该员工的领导的id(也是员工id) ——外键dept_id
:该员工的部门id(部门表外键) ——外键employee_name
创建部门表
-- 创建部门表 - 设置字符集的那种
CREATE TABLE IF NOT EXISTS department (
id INT PRIMARY KEY,
dept_name VARCHAR(20)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
插入数据
-- 部门表 插入数据
INSERT INTO department (id, dept_name) VALUES (1, '研发部');
INSERT INTO department (id, dept_name) VALUES (2, '营销部');
INSERT INTO department (id, dept_name) VALUES (3, '售后部');
创建员工表
-- 创建员工表
CREATE TABLE employee (
id INT PRIMARY KEY,
employee_name VARCHAR(20),
dept_id INT,
leader_id INT
)
-- 添加外键 / 设置级联更新、级联删除
ALTER TABLE employee
ADD CONSTRAINT fk_employee_department FOREIGN KEY