mysql级联删除

mysql级联删除

场景：

员工表

• id：员工id
• leader_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 (dept_id) REFERENCES department(id)
ON UPDATE CASCADE;

ALTER TABLE employee 
ADD CONSTRAINT fk_employee_employee FOREIGN KEY (leader_id) REFERENCES employee(id)
ON DELETE CASCADE;

查看索引的语句 SHOW INDEX FROM employee

-- 在员工表中插入数据
INSERT INTO employee (id, employee_name, dept_id) VALUES(2, 'John', 1);
INSERT INTO employee (id, employee_name, dept_id) VALUES(3, 'Rose', 3);
INSERT INTO employee (id, employee_name, dept_id) VALUES(4, 'Jenny', 2);
INSERT INTO employee (id, employee_name, dept_id) VALUES(5, 'Cris', 2);
INSERT INTO employee (id, employee_name, dept_id) VALUES(7, 'Jack', 1);
INSERT INTO employee (id, employee_name, dept_id) VALUES(9, 'Fish', 3);
INSERT INTO employee (id, employee_name, dept_id) VALUES(6, 'Summer', 1);

UPDATE employee SET leader_id=4 WHERE id=2;
UPDATE employee SET leader_id=4 WHERE id=3;
UPDATE employee SET leader_id=3 WHERE id=4;
UPDATE employee SET leader_id=2 WHERE id=5;
UPDATE employee SET leader_id=2 WHERE id=7;
UPDATE employee SET leader_id=5 WHERE id=9;
UPDATE employee SET leader_id=4 WHERE id=6;

-- 删除员工表中的id=2的数据
DELETE FROM employee WHERE id=2;

结果

假如主键和外键串联起来呢？

-- 插入数据
-- 外加的数据
INSERT INTO employee (id, employee_name, dept_id) VALUES(8, 'Summer2', 1);
INSERT INTO employee (id, employee_name, dept_id) VALUES(10, 'Summer3', 1);
UPDATE employee SET leader_id=9 WHERE id=8;
UPDATE employee SET leader_id=8 WHERE id=10;

删除员工id为2的员工

结果

为什么要级联操作？

• 如果要修改外键的值，修改时十分麻烦：需要先将引用的外键
  • 级联更新：【例】我将部门的id更新后，员工表中部门外键也一同更新
• 如果要研发部门和其部下的员工，需要先删除部门下的所有员工，再将该部门删除
  • 级联删除：【例】属于研发部门和其部下的员工全删除，只需要在外键表中删除外键数据即可，关联的表中的数据一并删除

-- 外键约束：foreign key，让表与表产生关系
# 1. 在创建时，可以添加外键
CREATE TABLE 表名 (
	...
	外键列
	CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称)
);
# 【例】
CREATE TABLE IF NOT EXISTS stuinfo (
	id INT PRIMARY KEY,
	stuname VARCHAR(20) NOT NULL,
	gender CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);


# 2. 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;


# 3. 创建表之后，添加外键，设置级联更新
ALTER TABLE ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

# 4. 级联操作
-- 1. 添加级联操作
	语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称
		FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) 
		ON UPDATE CASCADE 
		ON DELETE CASCADE;
-- 2. 分类：
	1. 级联更新：ON UPDATE CASCADE
	2. 级联删除：ON DELETE CASCADE
	

【注】mysql 列名不用驼峰。
因为数据库字段、索引对大小写是不敏感的，驼峰标识无意义；
所以一般采用数据库字段下划线， 实体类驼峰的命名方式