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;