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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With