时间戳

首页 > 解决方案 > 如何在简单的三向关系上实现基于约束的参照完整性

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

问题描述

我有三个简单的关系。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触发器删除条目不起作用，因为您不能在具有删除级联约束的任何表上使用触发器。TableCTableBinstead 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 作为映射表来实现这一点。

标签： sqlsql-serversql-server-2016

解决方案

您可以在将 EditedBy UserId 更新为 NULL 的 Users 表上创建一个而不是 delete 触发器：

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

推荐阅读