How to implement constraint based referential integrity on a simple 3 way relation

Question

I have three simple relations. TableB and TableC both reference TableA, and TableC also references TableB.

I'm finding it impossible to model this in SQL Server in a way that enforces referential integrity via constraints, but which also permits deletion of records from any entity without requiring either complex and inefficient trigger based referential integrity checking, or deleting related entities manually in the correct order.

Here is my schema.

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)
)

The final on delete cascade is the thing that ruins it as SQL Server will not permit it. Attempting to break this cycle I've tried the following.

Using a set null constraint and an after trigger to delete rows in TableC. Doesn't work, SQL Server refuses to allow this.

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

Using an Instead of trigger to delete TableC entries when TableB entries are deleted doesn't work because you cannot use an instead of trigger on any table with a delete cascade constraint.

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

An after trigger won't work because the attempt to delete from TableB will fail due to the foreign key on TableC.BRef before the trigger is even executed.

One solution is to encode the entire referential integrity checking using triggers, which works but is hideously complex and inefficient.

Another solution is to require clients to manually delete TableC entries before TableB entries.

Possibly the best solution I have at the moment is creating a stored procedure to delete from TableB and in that procedure manually deleting TableC entries first. But we don't currently use any stored procedures so having to start using them to solve what on the face of it seems like a very simple design issue is not ideal.

Are there any other solutions to this that I've overlooked?

UPDATE

Here is a more 'real world' version of what I'm trying to achieve.

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,   
)

In this schema deleting a User should delete any Documents where the user is the CreateBy. But deleted Users that map to the LastEditedBy for a document should just return null. I'm trying to achieve this using Documents_LastEditedBy as a mapping table.

Answer 1

You could create an instead of delete trigger on the Users table which updates the EditedBy UserId to 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