Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Can you use real names for the tables? I doubt you have table1 and table2 in your database. –  ypercube Mar 7 at 9:50
    
Well I've tried to reproduce my problem with these tables... So yeah, I use these tables in my database now. –  Michael Mar 7 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.) –  ypercube Mar 7 at 10:36
    
Thanks, Mikael also suggested this. –  Michael Mar 7 at 10:52

1 Answer 1

up vote 2 down vote accepted

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);
share|improve this answer
    
Thanks, this should solve the problem! Do you perhaps know the unlaying thought, of why a table can only appear once? –  Michael Mar 7 at 10:49
1  
@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. –  Mikael Eriksson Mar 7 at 10:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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