15.1-mysql基础-约束知识点补充

/*级联更新和级联删除
*/
USE students;
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(majorid) REFERENCES major(id);


SELECT * FROM major;
INSERT INTO major
VALUES(1,'mysql'), (2, 'python'),
(3,'java');


SELECT * FROM stuinfo;
INSERT INTO stuinfo
SELECT 1,'A','男',NULL,NULL,1 UNION ALL
SELECT 2,'B','女',NULL,NULL,1 UNION ALL
SELECT 3,'C','女',NULL,NULL,2 UNION ALL
SELECT 4,'D','男',NULL,NULL,2 UNION ALL
SELECT 5,'E','女',NULL,NULL,2 UNION ALL
SELECT 6,'F','男',NULL,NULL,3 UNION ALL
SELECT 7,'G','男',NULL,NULL,3


#删除专业表的3号专业
#直接删主表会报错Cannot delete or update a parent row: a foreign key constraint fails (`students`.`stuinfo`, CONSTRAINT `fk_stu_major` FOREIGN KEY (`majorid`) REFERENCES `major` (`id`))


#方式1：级联删除(on delete cascade 级联删除外键)
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
DELETE FROM major WHERE id=3;  #会将主表major中id为3的记录删除，同时删除stuinfo表中外键为3的记录
#方式2：级联置空(on delete set null 级联置空外键)
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
DELETE FROM major WHERE id=3;  #会将主表major中id为3的记录删除，同时将stuinfo表中的等值外键栏设为null