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:


ID  Text
1   Hoi
2   Hoi


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?

  • Can you use real names for the tables? I doubt you have table1 and table2 in your database. Mar 7, 2014 at 9:50
  • Well I've tried to reproduce my problem with these tables... So yeah, I use these tables in my database now. Mar 7, 2014 at 10:21
  • I answered in fury and then noticed that it's for SQL-Server and that you had already tried to set 2 delete rules. This is not possible as far as I know in SQL-server. You'll probably have to use a DELETE trigger in Table1 or write a stored procedure for the delete (which should first set the related values in Table2 to Null, then delete in Table1.) Mar 7, 2014 at 10:36

1 Answer 1


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

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);
  • Thanks, this should solve the problem! Do you perhaps know the unlaying thought, of why a table can only appear once? Mar 7, 2014 at 10:49
  • 2
    @Michael I have only heard speculation and that goes in the line of "better safe than sorry". If multiple paths was allowed you could easily end up in scenarios where SQL Server tries to delete a row twice. Or if you have two tables referencing each other you can get a kind of recursive delete. In order to not have to deal with that when the query is executed it is prevented altogether. I believe other vendors allow the declaration and handles the scenarios above as runtime errors instead. Mar 7, 2014 at 10:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.