>>Hi,
>>
>>When creating a FOREIGN KEY constraint in SSMS I get message that:
>>
>>…. may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION,
>>or modify other FOREIGN KEY constraints.
>>
>>
>>If I understand correctly the problem is that the table where I am attempting to set a Foreign key constraint has a dependency on some other table that creates a conflict. My tables have many links. So my question is, how can I find out which table has a link to this table that I need to change? How do I find out?
>>Thank you for any other suggestions.
>
>This is crazy. I find that two foreign keys in a table, seemingly absolutely not related, cause the problem. I have to add NO ACTION on delete and update to one or the other. But I don't see why.
You will probably find that the two tables referenced by the FKs *are related*. Simple case:
TableC has FKs to TableA and TableB both with cascading deletes.
TableB has FK to TableA. If you now attempt to set cascading deletes in this you will get the error since there are two paths by which TableC rows can be deleted - directly by a TableA deletion and also indirectly following the cascaded deletion of rows in TableB
But I don't know a simple way to track down the problem when the paths are not so obvious.....