配置CompartmentRelation外键级联删除触发循环约束错误的解决咨询

我有三张表：Compartment（舱室表）、CompartmentRelation（舱室关联表）和CompartmentRelationType（关联类型表）。其中CompartmentRelation用来存储舱室间的关联关系（比如上下、前后），CompartmentRelationType定义关联位置的类型。

我已经在Compartment表中创建了comp-1、comp-2、comp-3、comp-4这几个舱室，并在CompartmentRelation中录入了comp-1的上方关联舱室是comp-2和comp-3。

现在想给CompartmentRelation表的RelatedCompId字段设置删除级联操作时，抛出以下异常：

Unable to create relationship 'FK_CompartmentRelation_Compartment1'
Introducing FOREIGN KEY constraint 'FK_CompartmentRelation_Compartment1' on table 'CompartmentRelation' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.

附带的表结构SQL代码：

CREATE TABLE [dbo].[Compartment] (
    [Id]           INT            IDENTITY (1, 1) NOT NULL,
    [Name]         NVARCHAR (500) NOT NULL,
    CONSTRAINT [PK_Compartment] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[CompartmentRelation] (
    [Id]             INT IDENTITY (1, 1) NOT NULL,
    [CompId]         INT NOT NULL,
    [RelationTypeId] INT NOT NULL,
    [RelatedCompId]  INT NOT NULL,
    CONSTRAINT [PK_CompartmentRelation] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_CompartmentRelation_CompartmentRelationType] FOREIGN KEY ([RelationTypeId]) REFERENCES [dbo].[CompartmentRelationType] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_CompartmentRelation_Compartment1] FOREIGN KEY ([RelatedCompId]) REFERENCES [dbo].[Compartment] ([Id]),
    CONSTRAINT [FK_CompartmentRelation_Compartment] FOREIGN KEY ([CompId]) REFERENCES [dbo].[Compartment] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE [dbo].[CompartmentRelationType] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [Name]     NVARCHAR (200) NOT NULL,
    [NameLan1] NVARCHAR (200) NOT NULL,
    [NameLan2] NVARCHAR (200) NULL,
    CONSTRAINT [PK_CompartmentRelationType] PRIMARY KEY CLUSTERED ([Id] ASC)
);

这个错误是因为SQL Server不允许多条级联删除路径指向同一个表。这里CompartmentRelation同时通过CompId和RelatedCompId两个外键关联到Compartment，如果两个外键都设置ON DELETE CASCADE，删除一个Compartment记录时，会通过两条路径触发CompartmentRelation的删除，导致冲突。

有两种可行的解决方式：

方式一：修改其中一个外键的级联行为 #

把RelatedCompId对应的外键约束改成ON DELETE NO ACTION（默认就是这个），或者ON DELETE SET NULL（如果允许RelatedCompId为NULL的话）。修改后的SQL如下：

ALTER TABLE [dbo].[CompartmentRelation]
DROP CONSTRAINT [FK_CompartmentRelation_Compartment1];

ALTER TABLE [dbo].[CompartmentRelation]
ADD CONSTRAINT [FK_CompartmentRelation_Compartment1]
FOREIGN KEY ([RelatedCompId]) REFERENCES [dbo].[Compartment] ([Id])
ON DELETE NO ACTION ON UPDATE CASCADE; -- 或者用ON DELETE SET NULL（需要先把RelatedCompId设为可空）

如果要使用ON DELETE SET NULL，需要先修改RelatedCompId字段允许为空：

ALTER TABLE [dbo].[CompartmentRelation] ALTER COLUMN [RelatedCompId] INT NULL;

方式二：改用触发器实现级联逻辑 #

如果业务上必须让CompId和RelatedCompId都触发级联删除，可以用替代触发器来实现，避免SQL Server的多路径限制。示例触发器代码：

CREATE TRIGGER trg_Compartment_Delete_Relations
ON [dbo].[Compartment]
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    -- 删除关联表中所有涉及被删除舱室的记录
    DELETE FROM [dbo].[CompartmentRelation]
    WHERE CompId IN (SELECT Id FROM DELETED)
       OR RelatedCompId IN (SELECT Id FROM DELETED);
    -- 最后删除舱室本身
    DELETE FROM [dbo].[Compartment]
    WHERE Id IN (SELECT Id FROM DELETED);
END;

使用触发器时，需要把原来两个外键的ON DELETE CASCADE都改成ON DELETE NO ACTION，避免和触发器逻辑冲突。

内容的提问来源于stack exchange，提问作者şahin