mysql中的级联删除和级联置空和级联更新

mysql中的级联删除和级联置空和级联更新

我就直接po代码了，或者看我这篇图文并茂版

#mysql中的级联删除和级联置空
#
CREATE TABLE nativePlace(
	id INT PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
	province VARCHAR(30) COMMENT '省份',
	city VARCHAR(30) COMMENT '市/区',
	county VARCHAR(30) COMMENT '县',
	Township VARCHAR(30) COMMENT '乡/镇',
	address VARCHAR(100) COMMENT '家庭住址'
)COMMENT = '籍贯表';
#
SELECT * FROM nativePlace;
#
INSERT INTO nativePlace VALUES
(NULL,'江西省', '赣州市', '于都县', NULL, '渡江大道66666号'),
(NULL,'北京市', '海定区', 'A县', NULL, '黄埔路8888号'),
(NULL,'湖南省', '长沙市', 'C县', NULL, '南京路9999号'),
(NULL,'湖北省', '武汉市', 'D县', NULL, '人民路5555号'),
(NULL,'江苏省', '南京市', 'F县', NULL, '长安路8686号');
#

#
CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
	idCard VARCHAR(20) UNIQUE NOT NULL COMMENT '身份证号',
	pname VARCHAR(100) COMMENT '姓名',
	gender VARCHAR(1) COMMENT '性别',
	birthday DATETIME COMMENT '出生日期',
	nation VARCHAR(30) COMMENT '民族',
	nativePlaceId INT COMMENT '籍贯'
)COMMENT = '户口信息表';
#
SELECT * FROM person;

#
INSERT INTO person VALUES
(NULL, '420102200808270010', '令狐冲', '男', '2008-08-27', '汉族', 4),
(NULL, '360731199803120010', '韦小宝', '男', '1998-03-12', '汉族', 1),
(NULL, '360731199605210010', '张无忌', '男', '1996-05-21', '汉族', 1),
(NULL, '420102199506080010', '杨过', '女', '1995-06-08', '汉族', 4),
(NULL, '320100199311120010', '段誉', '男', '1993-11-12', '汉族', 5),
(NULL, '430101199201250010', '乔峰', '女', '1992-01-25', '汉族', 3);

#
DESC person;
DESC nativePlace;
SHOW CREATE TABLE person;
SHOW CREATE TABLE nativePlace;
SHOW FULL COLUMNS FROM nativePlace;
SHOW FULL COLUMNS FROM person;
#

#传统的方式添加外键
ALTER TABLE person ADD CONSTRAINT fk_person_nativePlace FOREIGN 
KEY(nativePlaceId) REFERENCES nativePlace(id);

#理论上来说，我们应该是先删除从表，再删除主表，但是我们可以通过级联删除来强制删除主表
#注意：级联删除和级联置空是写在从表，如下：
#方式一：级联删除(删除主表记录的同时删除从表相关联记录，心狠手辣型)
ALTER TABLE person ADD CONSTRAINT fk_person_nativePlace FOREIGN 
KEY(nativePlaceId) REFERENCES nativePlace(id) ON DELETE CASCADE;

#删除外键
ALTER TABLE person DROP FOREIGN KEY fk_person_nativePlace;

#
SHOW INDEX FROM person;

#方式二：级联置空(删除主表记录的同时将从表相关联记录的外键的值置为null，温柔留有余地型)
ALTER TABLE person ADD CONSTRAINT fk_person_nativePlace FOREIGN 
KEY(nativePlaceId) REFERENCES nativePlace(id) ON DELETE SET NULL;

#删除外键
ALTER TABLE person DROP FOREIGN KEY fk_person_nativePlace;

#级联更新
ALTER TABLE person ADD CONSTRAINT fk_person_nativePlace FOREIGN 
KEY(nativePlaceId) REFERENCES nativePlace(id) ON UPDATE CASCADE;

#删除外键
ALTER TABLE person DROP FOREIGN KEY fk_person_nativePlace;

#级联更新置空
ALTER TABLE person ADD CONSTRAINT fk_person_nativePlace FOREIGN 
KEY(nativePlaceId) REFERENCES nativePlace(id) ON UPDATE SET NULL;

#
SELECT * FROM person;
SELECT * FROM nativePlace;

#删除主表记录
DELETE FROM nativePlace WHERE id = 4;

#删除主表记录
DELETE FROM nativePlace WHERE id = 1;

#更新主表中的主键
UPDATE nativePlace SET id = 666 WHERE id = 5;

#更新主表中的主键
UPDATE nativePlace SET id = 888 WHERE id = 666;