Foreign key constraint may cause cycles or multiple cascade paths?

willcascadeondelete
error number:1785,state:0,class:16
specify on delete no action or on update no action,
ef core foreign key constraint
failure to specify cascading delete when enforcing referential integrity can cause what problem
ef core disable cascade delete
fk_dbo
ef core remove foreign key

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 how I can do this?

SQL Server does simple counting of cascade paths and, rather than trying to work out whether any cycles actually exist, it assumes the worst and refuses to create the referential actions (CASCADE): you can and should still create the constraints without the referential actions. If you can't alter your design (or doing so would compromise things) then you should consider using triggers as a last resort.

FWIW resolving cascade paths is a complex problem. Other SQL products will simply ignore the problem and allow you to create cycles, in which case it will be a race to see which will overwrite the value last, probably to the ignorance of the designer (e.g. ACE/Jet does this). I understand some SQL products will attempt to resolve simple cases. Fact remains, SQL Server doesn't even try, plays it ultra safe by disallowing more than one path and at least it tells you so.

Microsoft themselves advises the use of triggers instead of FK constraints.

Foreign key constraint may cause cycles or multiple cascade paths , Foreign key constraint 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 . Introducing FOREIGN KEY constraint 'ConstraintName' on table 'TableName' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. I was however using EF5 Code First so I fist needed to figure out why my model was giving me back this error.

A typical situation with multiple cascasing paths will be this: A master table with two details, let's say "Master" and "Detail1" and "Detail2". Both details are cascade delete. So far no problems. But what if both details have a one-to-many-relation with some other table (say "SomeOtherTable"). SomeOtherTable has a Detail1ID-column AND a Detail2ID-column.

Master { ID, masterfields }

Detail1 { ID, MasterID, detail1fields }

Detail2 { ID, MasterID, detail2fields }

SomeOtherTable {ID, Detail1ID, Detail2ID, someothertablefields }

In other words: some of the records in SomeOtherTable are linked with Detail1-records and some of the records in SomeOtherTable are linked with Detail2 records. Even if it is guaranteed that SomeOtherTable-records never belong to both Details, it is now impossible to make SomeOhterTable's records cascade delete for both details, because there are multiple cascading paths from Master to SomeOtherTable (one via Detail1 and one via Detail2). Now you may already have understood this. Here is a possible solution:

Master { ID, masterfields }

DetailMain { ID, MasterID }

Detail1 { DetailMainID, detail1fields }

Detail2 { DetailMainID, detail2fields }

SomeOtherTable {ID, DetailMainID, someothertablefields }

All ID fields are key-fields and auto-increment. The crux lies in the DetailMainId fields of the Detail tables. These fields are both key and referential contraint. It is now possible to cascade delete everything by only deleting master-records. The downside is that for each detail1-record AND for each detail2 record, there must also be a DetailMain-record (which is actually created first to get the correct and unique id).

Entity Framework: how to solve "FOREIGN KEY constraint may , You're getting this because Country has a link to State, and VendorDetails has a link to both Country and State. This gives multiple paths  When running the migration tool to update the database, I get the following error: Introducing FOREIGN KEY constraint 'FK_dbo.Players_dbo.Teams_TeamNationalId' on table 'Players' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint.

I would point out that (functionally) there's a BIG difference between cycles and/or multiple paths in the SCHEMA and the DATA. While cycles and perhaps multipaths in the DATA could certainly complicated processing and cause performance problems (cost of "properly" handling), the cost of these characteristics in the schema should be close to zero.

Since most apparent cycles in RDBs occur in hierarchical structures (org chart, part, subpart, etc.) it is unfortunate that SQL Server assumes the worst; i.e., schema cycle == data cycle. In fact, if you're using RI constraints you can't actually build a cycle in the data!

I suspect the multipath problem is similar; i.e., multiple paths in the schema don't necessarily imply multiple paths in the data, but I have less experience with the multipath problem.

Of course if SQL Server did allow cycles it'd still be subject to a depth of 32, but that's probably adequate for most cases. (Too bad that's not a database setting however!)

"Instead of Delete" triggers don't work either. The second time a table is visited, the trigger is ignored. So, if you really want to simulate a cascade you'll have to use stored procedures in the presence of cycles. The Instead-of-Delete-Trigger would work for multipath cases however.

Celko suggests a "better" way to represent hierarchies that doesn't introduce cycles, but there are tradeoffs.

Error message 1785 occurs when you create a FOREIGN KEY , [SOLVED] - Entity Framework: how to solve "FOREIGN KEY constraint may cause cycles or multiple cascade paths"? By making those Navigation Properties you have implicitly instructed EF Code First to create Foreign Key constraints for you and each of these will have cascading deletes on them. This means, upon deleting a Year, the database will cascade that delete to that Year's Months and in turn those Month's Days.

There is an article available in which explains how to perform multiple deletion paths using triggers. Maybe this is useful for complex scenarios.

http://www.mssqltips.com/sqlservertip/2733/solving-the-sql-server-multiple-cascade-path-issue-with-a-trigger/

SQL Error: Introducing FOREIGN KEY constraint may cause cycles , Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths. – Learn more on the SQLServerCentral forums. Server: Msg 1785, Level 16, State 1, Line 1 Introducing FOREIGN KEY constraint 'fk_two' on table 'table2' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Server: Msg 1750, Level 16, State 1, Line 1 Could not create constraint. See previous errors.

By the sounds of it you have an OnDelete/OnUpdate action on one of your existing Foreign Keys, that will modify your codes table.

So by creating this Foreign Key, you'd be creating a cyclic problem,

E.g. Updating Employees, causes Codes to changed by an On Update Action, causes Employees to be changed by an On Update Action... etc...

If you post your Table Definitions for both tables, & your Foreign Key/constraint definitions we should be able to tell you where the problem is...

Server: Msg 1785, Level 16, State 1, Line 1 Introducing FOREIGN KEY constraint 'fk_two' on table 'table2' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Msg 1785, Level 16, State 0, Line 3Introducing FOREIGN KEY constraint 'FK_Table' on table 'Table' 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 0, Line 3Could not create constraint.

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  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.

[SOLVED] - SQL Error: Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths. Entity Framework Core. Introducing FOREIGN KEY constraint 'FK_AnEventUsers_Users_UserId' on table 'AnEventUsers' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors. My entities are these:

I found a solution. The problem was with the Category, both of them had a foreign key to the same table with a delete cascade, thus, if I deleted  Very tedious and frustrating these multiple cascade paths are, and yet I do understand the reason for them. With that in mind, I'm having some trouble design the flow of my database, and as I keep coming across these pesky "multiple cascade path" rules, I'm presenting the situation here, in the hopes that somebody can point out a better flow that A) Will Achieve my Aim B) SQL Server will allow

Comments
  • One of the solution is here
  • one thing that i still cannot understand is that, if this "problem" can be solved by using a trigger, then how come that a trigger will not "cause cycles or multiple cascade paths ..." ?
  • @armen: because your trigger will explicitly supply the logic that the system couldn't implicitly figure out on it's own eg if there are multiple paths for a delete referential action then your trigger code will define which tables are deleted and in which order.
  • And also the trigger executes after the first operation completes so there is no race going on.
  • @dumbledad: I mean, only use triggers when constraints (maybe on combination) can't get the job done. Constraints are declarative and their implementations are the responsibility of the system. Triggers are procedural code and you must code (and debug) the implementation and endure their disadvantages (worse performance etc).
  • The problem with this is that the trigger only works as long as you remove the foreign key constraint, which means you then have no referential integrity checking on database inserts and so you need even more triggers to handle that. The trigger solution is a rabbit hole leading to a degenerate database design.
  • Your comment helped me a lot to understand the problem that I am facing. Thank you! I would prefer turning off the cascade delete for one of the path, then handle delete of other records some other ways(stored procedures; triggers; by code etc). But I keep your solution(grouping in one path) in mind for possible different applications of same problem...
  • One up for use of the word crux (and also for explaining)
  • Is this better than writing triggers? It seems odd to add an additional table just to get the cascade working.
  • Anything is better than writing triggers. Their logic is opaque and they are inefficient compared to anything else. Breaking large tables into smaller ones for finer control is just a natural consequence of a better normalized database and not in itself something to be concerned by.
  • "if you're using RI constraints you can't actually build a cycle in the data!" -- good point!
  • Sure you can build data circularity, but with MSSQL only using UPDATE. Other RDBMs support deferred constraints (integrity ensured at time of commit, not at time of insert/update/delete).