SQL Server 两列如何引用同一主键并实现级联删除?
需要为 Tbl1_idx1 和 Tbl1_idx2 分别建立外键。SQL Server 不允许这两个外键同时形成从 Table1 到 Table2 的删除级联路径。要在删除 Table1 记录时自动清理两列引用的关联行,可以将两个外键都设为 ON DELETE NO ACTION,再在 Table1 上创建 INSTEAD OF DELETE 触发器,先删除关联行,然后删除父表记录。
为什么一个复合外键不能满足要求?
复合外键按列的位置匹配父表中的一个复合键,引用列和被引用列的数量必须一致。下面的定义将两列作为一个整体,却只引用了一列,因此会报错:
FOREIGN KEY ([Tbl1_idx1], [Tbl1_idx2])
REFERENCES [Table1]([idx])
在 REFERENCES 中重复写两次 idx,也不能建立两个独立的引用。这里需要两个外键:
Tbl1_idx1引用Table1.idx。Tbl1_idx2也引用Table1.idx,可以指向另一条记录。
Table2 上的联合唯一约束只限制两列的值组合不能重复,不影响两个外键分别引用哪条记录,因此可以保留。
为什么两个级联外键会报错?
如果两个外键都设置了 ON DELETE CASCADE,删除一条 Table1 记录时就会产生两条通往 Table2 的级联路径。SQL Server 会对这种结构报 multiple cascade paths 错误,即使现有数据中两列从未引用同一条记录,也不能绕过这一限制。参见 Microsoft 对错误 1785 的说明。
只把其中一个外键改为 NO ACTION 也不够:如果待删除记录只被该外键引用,外键约束仍会阻止删除父表记录。
第一步:分别建立两个非级联外键
以下示例假设两张表位于 dbo schema,相关列的数据类型满足外键要求。执行前,需要移除已经成功创建的相关外键,并使用实际约束名。下面的代码用于创建替代约束。
ALTER TABLE [dbo].[Table2] WITH CHECK
ADD CONSTRAINT [FK_Table2_Tbl1_idx1]
FOREIGN KEY ([Tbl1_idx1])
REFERENCES [dbo].[Table1] ([idx])
ON DELETE NO ACTION;
ALTER TABLE [dbo].[Table2] WITH CHECK
ADD CONSTRAINT [FK_Table2_Tbl1_idx2]
FOREIGN KEY ([Tbl1_idx2])
REFERENCES [dbo].[Table1] ([idx])
ON DELETE NO ACTION;
这两个外键仍会检查新增或修改的引用值。NO ACTION 不会关闭引用完整性检查,也不会自动清理子表记录。WITH CHECK 会检查已有数据;如果存在无效引用,应先修复。相关行为见 Microsoft 的外键约束文档。
第二步:用触发器先删子表,再删父表
假设 Table1 尚未定义 INSTEAD OF DELETE 触发器,也没有其他与该触发器冲突的约束或删除逻辑,可以创建以下触发器:
CREATE TRIGGER [dbo].[TR_Table1_Delete]
ON [dbo].[Table1]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
DELETE t2
FROM [dbo].[Table2] AS t2
WHERE EXISTS
(
SELECT 1
FROM deleted AS d
WHERE t2.[Tbl1_idx1] = d.[idx]
OR t2.[Tbl1_idx2] = d.[idx]
);
DELETE t1
FROM [dbo].[Table1] AS t1
INNER JOIN deleted AS d
ON t1.[idx] = d.[idx];
END;
CREATE TRIGGER 需要作为一个批次的第一条语句执行,因此应与前面的 ALTER TABLE 分批提交。
触发器按以下顺序处理:
- 从
deleted中取得本次准备删除的全部 Table1 记录。 - 删除 Table2 中任意一个引用列命中这些记录的行。
- 删除对应的 Table1 记录。
这里必须使用 OR:只要任意一列引用了待删除记录,就需要删除整条 Table2 记录。如果一条子表记录的两列都命中,该记录也只会被删除一次。EXISTS 会处理整个 deleted 集合,因此也支持一次删除多条父表记录。
SQL Server 对表上的 INSTEAD OF 触发器有专门规则:触发器内部再次对同一张表执行相同操作时,不会直接递归调用自身,而会执行实际操作及相应的约束检查。因此,最后的 DELETE t1 用于完成父表删除。AFTER DELETE 需要等外键检查成功后才能执行,无法在这里提前清理阻止删除的子表记录。参见 Microsoft 的 CREATE TRIGGER 文档。
使用前需要确认什么?
- 删除时会删除整条关系记录。例如 Table2 中存在
(10, 20),删除 Table1 的10会删除这条关系,但不会删除 Table1 的20。 - 如果 Table1 已有
INSTEAD OF DELETE触发器,应把逻辑合并进去;同一张表不能为同一种操作定义多个此类触发器。 - 如果还有其他表引用 Table1 或 Table2,需要一并考虑删除顺序,否则相关外键仍可能阻止删除。
- 如果两列都必须填写有效主键值,还需要将它们定义为
NOT NULL;外键本身不会禁止NULL。
上线前应测试仅第一列引用、仅第二列引用、两列同时引用,以及一次删除多条父表记录的情况。上述代码针对题目给出的两表关系,仍需结合实际 schema、其他外键和已有触发器验证。
备注:内容仅供参考。
