SQL Server删除主表数据，与之关联的字表数据也删除

一：创建 person、part 表,创建触发器，准备数据

-- 创建 part 表
CREATE TABLE part (
	id nvarchar(40) COLLATE Chinese_PRC_CS_AI_WS NOT NULL,
	part_name nvarchar(50) COLLATE Chinese_PRC_CS_AI_WS NULL,
	part_address nvarchar(50) COLLATE Chinese_PRC_CS_AI_WS NULL,
	CONSTRAINT PK_PART PRIMARY KEY (id)
);

-- 创建 person 表
CREATE TABLE person (
	id nvarchar(40) COLLATE Chinese_PRC_CS_AI_WS NOT NULL,
	peroson_name nvarchar(50) COLLATE Chinese_PRC_CS_AI_WS NULL,
	phone_number nvarchar(50) COLLATE Chinese_PRC_CS_AI_WS NULL,
	address nvarchar(50) COLLATE Chinese_PRC_CS_AI_WS NOT NULL,
	part_id nvarchar(40) COLLATE Chinese_PRC_CS_AI_WS NOT NULL,
	CONSTRAINT PK_PERSON PRIMARY KEY (id)
);

二：为 person 表创建触发器

-- 创建触发器
ALTER TABLE person ADD CONSTRAINT fk_person_part_id FOREIGN KEY (part_id) REFERENCES part(id) ON DELETE CASCADE ON UPDATE CASCADE;

三：插入测试数据

INSERT INTO part (id, part_name, part_address)
VALUES 	('1122', 'part1', 'this is a test part address1'),
		('2233', 'part2', 'this is a test part address2');

INSERT INTO person (id, peroson_name, phone_number, address, part_id)
VALUES 	('1', 'test1', '1234567890', 'this is a test address1', '1122'),
		('2', 'test2', '1258745587', 'this is a test address2', '2233'),
		('3', 'test3', '1589645415', 'this is a test address2', '1122');

四：验证结果

删除person 表中id为1 的数据

-- 删除 person
DELETE FROM person WHERE id=N'1';

查看结果：

删除part 表中id为1122 的数据，

-- 删除 part
DELETE FROM part WHERE id=N'1122';

**查看结果：**part表中id=1122 的数据被删除，与之关联的person 表中part_id=1122 的数据也被删除了