MySQL级联删除与级联置空

# 约束外键中，为了删除主表，级联删除与级联置空

SHOW INDEX FROM major;
SHOW INDEX FROM stuinfo;
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;

# 传统的方式添加外键
ALTER TABLE stuinfo 
ADD CONSTRAINT fk_stu_major FOREIGN KEY(major_id) REFERENCES major(id);

SELECT * FROM major;

INSERT INTO major
VALUES(1,'java'),(2,'H5'),(3,'C');

SELECT * FROM stuinfo;

INSERT INTO stuinfo
SELECT 1,'john1','女',NULL,NULL,1 UNION ALL
SELECT 2,'john2','女',NULL,NULL,1 UNION ALL
SELECT 3,'john3','女',NULL,NULL,2 UNION ALL
SELECT 4,'john4','女',NULL,NULL,2 UNION ALL
SELECT 5,'john5','女',NULL,NULL,1 UNION ALL
SELECT 6,'john6','女',NULL,NULL,3 UNION ALL
SELECT 7,'john7','女',NULL,NULL,3 UNION ALL
SELECT 8,'john8','女',NULL,NULL,1;

# 删除专业表的三号专业
# 但不能直接删除，因为删除要先删从表

# ①级联删除
ALTER TABLE stuinfo 
ADD CONSTRAINT fk_stu_major FOREIGN KEY(major_id) REFERENCES major(id) ON DELETE CASCADE;


DELETE FROM major WHERE id=3;

# ②级联置空
ALTER TABLE stuinfo 
ADD CONSTRAINT fk_stu_major FOREIGN KEY(major_id) REFERENCES major(id) ON DELETE SET NULL;

DELETE FROM major WHERE id=2;