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.

. . Sample data would really help. Why can't the third relationship just be a look up from the first two? — Gordon Linoff, Feb 20 '20 at 15:43
I'm not sure what you mean. In my real world scenario we have a table that has multiple foreign key references to TableA. The behaviour we are seeking to achieve is for one of those FK references to implement delete cascade, and the other 2 FK references to implement delete set null. In a single table there is no way to remotely achieve that so this is a more normalized design that gets us most of the way there, but which still fails at the last hurdle. — Neutrino, Feb 20 '20 at 15:52
In the case where C.B.A always the same as C.A, you can model this by having C(Aid,Bid) reference B(Aid,Bid), and avoid the FK directly to A. — David Browne - Microsoft, Feb 20 '20 at 15:56
Think of it as `TableA` is `Users` with columns `UserName` etc. `TableB` is `Documents` with columns `CreatedBy` and `LastEditedBy`. If a users `UserName` changes that change needs to be picked up through the system naturally. If a document's `LastEditedBy` user is deleted, that field needs to effectively be null, but if a document's `CreatedBy` user is deleted the Document also needs to be deleted. — Neutrino, Feb 20 '20 at 15:57
Updated the post to include a more real world description of the problem. — Neutrino, Feb 20 '20 at 16:09
Interesting.. On [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlite_3.8&fiddle=1e5dc6f4e546b9d984a3caebf2e02519) Firebird, MariaDB, MySQL, Oracle, Postgres and SQLite in all available versions all accept this schema. Only SQL-Server complains. I tend to consider that as a (design) bug. — Paul Spiegel, Feb 26 '20 at 18:14

score 1 · Answer 1 · answered Feb 20 '20 at 17:06

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

The problem with that is that no table with an `instead of` trigger can also have a cascade constraint. So when you add an `instead of` to one table you end up having to replace any cascade constraints with `instead of` triggers all the way up the parent->child table graph. And you can't use `after` triggers if the table has a foreign key, so doing it that way means replacing all the foreign key constraints with after triggers to enforce referential integrity. Either way you end up in trigger hell. — Neutrino, Feb 21 '20 at 09:01
If SQL Server at least supported deferred constraints you'd be able to leave the foreign keys in place and break the cascade cycle with an `after` trigger, but with no deferred constraints and a hopelessly restrictive constraint cascade implementation it seems impossible to elegantly implement declarative referential integrity in even quite simple circumstances. — Neutrino, Feb 21 '20 at 09:06
you have a error : Msg 547, Level 16, State 0, Line 42 The DELETE statement conflicted with the REFERENCE constraint "F_Documents_UserId". The conflict occurred in database "test", table "dbo.Documents_LastEditedBy", column 'UserId'. — Amirhossein, Mar 03 '20 at 10:11

score 0 · Answer 2 · answered Mar 03 '20 at 10:31

Hope this helps you.

this relation is fault of design and wrong like this picture :

We don't need relation between users and document because this LastEDITEDBY job (you table B) better delete weed relation,

But for whatever reason you know yourself. If you had to have such a table.

The solution: you should use a new column called flag in Table a and c. then DELETE table b (this example name Documents_LastEditedBy) and Trriger after delete set flag of C and A True. now, delete same row in C using flag like Where flag=1 and then delete same row in A using Where flag=1 by trigger instead of.

I hope is usabel

score 0 · Answer 3 · answered Mar 04 '20 at 04:25

Documents tables examples are not so real as compared to TableA example.

Like I am not able to understand the purpose of table Documents_LastEditedBy in first place.So like there is such complicated situation like yours then these thing matter.

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

See there is 2 thing in your constraint,foreign key and delete cascade.

Purpose of Foriegn key is to maintain referential integrity and performance gain if your FK is trusted.

On Delete cascade :If parent table record is deleted then child table record will be automatically deleted.

SO one way is to partly implement FK constrain without Delete cascade.

Implement Delete Cascade in trigger : In fact Trigger is meant for situation like this.

Trigger and constraint both are hideous.You can throw or log error from trigger just like constraint.If any error you can rollBack and throw error.

Trigger is not so inefficient as you think,If code is properly written and index os fine tune.

BTW how often Delete will happen ? Worry for infficiency depend upon on this too.

Second Method : Since I am not sure about purpose of each table, my second approach give good idea.

My script are not tested,

You can redesign your tables in this manner,

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

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

create table Documents_UserMapping (
   Documents_UserMappingid int identity(1,1) primary key
   DocumentId int not null,
   UserId int not null,
   constraint F_Documents_UserId foreign key (UserId) references Users(Id) on delete cascade,
   UserType tinyint not null    
)

UserType : Indicate that userid belong to Documents table  or Documents_LastEditedBy

There is advantage and disadvantae of this design, but i am not discussing this.

Firstly I want to understand complete requirement before taking any decision.

As far as i understood I will go with Trigger approach.

Your question is excellent.

Jean-François Beaulieu · Answer 4 · 2020-03-04T14:39:54.450

0

Create a trigger to replace the delete on the user table:

CREATE TRIGGER [dbo].[Trigger_Users]
ON [dbo].[Users]
INSTEAD OF DELETE
AS
BEGIN

    SET NoCount ON
    SELECT Id INTO #TEMP FROM deleted

    DELETE FROM [dbo].[Documents_LastEditBy] 
    WHERE Exists(select top 1 1 FROM #TEMP WHERE Id = UserId)
    DELETE FROM [dbo].[Users] 
    WHERE Exists(select top 1 1 FROM #TEMP T WHERE T.Id = [Users].Id)
END

You don't need to call the delete on the [dbo].[Documents] table since it's will be deleted on cascade when User is delete

The constraints F_Documents_UserId should not have a cascade delete.

edited Mar 04 '20 at 14:39

answered Mar 04 '20 at 14:27

Jean-François Beaulieu

1
2

Documents_LastEditedBy is a mapping table, it correlates which user last edited the document. A user can be deleted, which should remove all Documents and associated Documents_LastEditedBy mapping entries, but a document can also be deleted which leaves the User and just remove the LastEditedBy mapping entries, and in this case a trigger on the Users table does not remove the mapping entries. – Neutrino Mar 06 '20 at 16:24

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

4 Answers4