SQL Server Table Relations – Two Relations to the Same Table in SQL Server

constraintrelational-theorysql server

I've got two tables. When I delete a row in Table1, I want to set the ID of that row in all the rows of Table2 to NULL. I've got two relationships on Table1.

The problem is: I can only set one delete rule: Set NULL. If I try to set the other it fails. But when I try to delete ID 1 from Table1, this now also fails.

My table data looks like the following:

Table1:

ID  Text
1   Hoi
2   Hoi

Table2:

ID  ForeignID1  ForeignID2
1   1   2
2   1   1

Two tables

How do I need to set my constraints, so it sets the Foreign IDs to NULL on Table2 when I delete the row in Table1?

Best Answer

It is not possible with a constraint. Have a look at Multiple Cascading Actions.

No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE.

You can use an instead of trigger instead where you do the update in Table2 before the delete in Table1.

The trigger could look something like this

create trigger Table1_Delete on Table1 instead of delete
as

update Table2
set Table1ID1 = null
where Table2.Table1ID1 in (select D.Table1ID from deleted as D);

update Table2
set Table1ID2 = null
where Table2.Table1ID2 in (select D.Table1ID from deleted as D);

delete from Table1 
where Table1.Table1ID in (select D.Table1ID from deleted as D);
Related Question