sql - 如何在简单的三向关系上实现基于约束的参照完整性

Question

我有三个简单的关系。 TableB和 TableC两者引用TableA , 和 TableC还引用 TableB .

我发现无法在 SQL Server 中以通过约束强制执行参照完整性的方式对此进行建模，但这也允许从任何实体中删除记录，而无需复杂且低效的基于触发器的参照完整性检查，或手动删除相关实体正确的顺序。

这是我的架构。

create table TableA (
   Id int identity not null,
   constraint P_TableA_Id primary key (Id)
)

create table TableB (
   Id int identity not null,
   constraint P_TableB_Id primary key (Id),

   ARef int,
   constraint F_TableB_ARef foreign key (ARef) references TableA(Id) on delete cascade
)

create table TableC (
   Id int identity not null,
   constraint P_TableC_Id primary key (Id),

   ARef int,
   constraint F_TableC_ARef foreign key (ARef) references TableA(Id) on delete cascade,

   BRef int,

   -- Does not work.
   --constraint F_TableC_BRef foreign key (BRef) references TableB(Id) on delete cascade

   -- Works.
   constraint F_TableC_BRef foreign key (BRef) references TableB(Id)
)

最后 on delete cascade是破坏它的东西，因为 SQL Server 不会允许它。为了打破这个循环，我尝试了以下方法。

使用 set null约束和 after触发器删除 TableC 中的行.不起作用，SQL Server 拒绝允许这样做。

constraint F_TableC_BRef foreign key (BRef) references TableB(Id) on delete set null

使用 Instead of触发删除 TableC条目何时 TableB条目被删除不起作用，因为您不能使用 instead of在具有删除级联约束的任何表上触发。

create trigger T_TableB_delete on TableB instead of delete as
begin
   delete from TableC where BRef in (select Id from deleted)
   delete from TableB where Id in (select Id from deleted)
end

安 after触发器将不起作用，因为尝试从 TableB 中删除由于 TableC.BRef 上的外键将失败在触发器执行之前。

一种解决方案是使用触发器对整个参照完整性检查进行编码，这种方法有效但极其复杂且效率低下。

另一种解决方案是要求客户端手动删除 TableC之前的条目 TableB条目。

可能我目前最好的解决方案是创建一个存储过程以从 TableB 中删除。并在该过程中手动删除 TableC首先条目。但是我们目前不使用任何存储过程，因此必须开始使用它们来解决表面上看起来非常简单的设计问题并不理想。

有没有其他我忽略的解决方案？

更新

这是我试图实现的更“真实世界”的版本。

create table Users (
   Id int identity not null,
   constraint P_Users_Id primary key (Id),

   Name nvarchar(20)
)

create table Documents (
   Id int identity not null,
   constraint P_Documents_Id primary key (Id),

   CreatedBy int,
   constraint F_Documents_CreatedBy foreign key (CreatedBy) references Users(Id) on delete cascade,
)

create table Documents_LastEditedBy (
   DocumentId int,
   constraint F_Documents_LastEditedBy_DocumentId foreign key (DocumentId) references Documents(Id) on delete cascade,

   UserId int,
   constraint F_Documents_UserId foreign key (UserId) references Users(Id) on delete cascade,   
)

在此模式中，删除用户应删除用户为 CreateBy 的所有文档。但是映射到文档的 LastEditedBy 的已删除用户应该只返回 null。我正在尝试使用 Documents_LastEditedBy 作为映射表来实现这一点。

Answer 1

您可以在 Users 表上创建一个而不是删除触发器，它将 EditedBy UserId 更新为 NULL :

create table Users (
   Id int identity not null,
   constraint P_Users_Id primary key (Id),

   Name nvarchar(20),
)
go

create table Documents (
   Id int identity not null,
   constraint P_Documents_Id primary key (Id),

   CreatedBy int,
   constraint F_Documents_CreatedBy foreign key (CreatedBy) references Users(Id) on delete cascade
)

create table Documents_LastEditedBy (
   DocumentId int,
   constraint F_Documents_LastEditedBy_DocumentId foreign key (DocumentId) references Documents(Id) on delete cascade,

   UserId int,
   constraint F_Documents_UserId foreign key (UserId) references dbo.Users(Id) on delete no action
)
go

insert into dbo.Users(Name) values ('UserA'), ('UserB');
insert into dbo.Documents(CreatedBy) values (1), (2); --doc1 created by userA, doc2 created by userB, doc3 created by 
insert into dbo.Documents_LastEditedBy values(1, 2) --document 1 edited by B (?? )
insert into dbo.Documents_LastEditedBy values(2, 1) --document 2 edited by userA
insert into dbo.Documents_LastEditedBy values(2, 2) --document 2 edited by userB
go

select *
from dbo.Users
select *
from dbo.Documents
select *
from Documents_LastEditedBy
go


delete from dbo.Users
where name = 'UserA' --fk violation
go

create trigger dbo.insteadofdeleteusers on dbo.users
instead of delete
as
begin
    if not exists(select * from deleted)
    begin
        return;
    end

    update dbo.Documents_LastEditedBy
    set UserId = null
    where UserId in (select id from deleted);

    delete 
    from dbo.Users
    where id in (select id from deleted);

end
go

delete from dbo.Users
where name = 'UserA' 
go

select *
from dbo.Users --userA gone
select *
from dbo.Documents--document created by userA gone
select *
from Documents_LastEditedBy --last edited userA set to NULL
go