Foreign key constraint may cause cycles or multiple cascade paths?

Question

I have a problem when I try to add constraints to my tables. I get the error:

Introducing FOREIGN KEY constraint 'FK74988DB24B3C886' on table 'Employee' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

My constraint is between a Code table and an employee table. The Code table contains Id, Name, FriendlyName, Type and a Value. The employee has a number of fields that reference codes so that there can be a reference for each type of code.

I need for the fields to be set to null if the code that is referenced is deleted.

Any ideas of how I can do this?

Answer 1

The counting of cascade paths is done by SQL Server and rather than trying to work out whether any cycles actually exist, It refuses to create the referential actions (CASCADE): you can also create the constraints without the referential actions.

But, if you can't alter your design (or doing so would compromise things) then you must use triggers as a last resort.

FWIW resolving cascade paths is a complex problem. 

Another SQL products just ignore the problem and allow you to create cycles, in that case, it will be a race to see which will overwrite the value last, due to the ignorance of the designer (e.g. Jet/ACE does this). 

However, some SQL products will attempt to resolve simple cases. But the fact remains the same, SQL Server doesn't even try, it doesn't allow more than one path and at least it tells you so.

Note: Microsoft supports the use of FK constraints instead of triggers.