SQL Server ON DELETE SET NULL error

Question

In my database, I have an addresses table, and this table is used as the primary table in foreign keys with other tables (eg. customers, employees, etc...)

This SQLFiddle shows the relationship between addresses and customers, where customers has to 2 addresses.

Currently the FK's are set up as ON DELETE NO ACTION and I started changing them to ON DELETE SET NULL.

This has worked fine for most tables where there is only one address column. On one particular table where there is just one address column and on all tables where there are multiple address columns,I'm getting the error:

Msg 1785, Level 16, State 1, Line 1
Introducing FOREIGN KEY constraint 'FK_customers_shipping_address' on table 'customers' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

If I'm not using CASCADE why am I getting this error? And is there a way around it?

Answer 1

SET NULL and CASCADE behave in the same way when it comes to this rule. You can't have "multiple cascade path". The message is a little unclear.

This is a SQL Server engine limitation. You can't do anything about it. Implement the cascading logic yourself.