Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q321843
SYMPTOMS
You may receive the following error message when you create a FOREIGN KEY constraint:
Server: Msg 1785, Level 16, State 1, Line 1Introducing 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 1Could not create constraint. See previous errors.
CAUSE
You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree.
WORKAROUND
To work around this problem, do not create a foreign key that will create more than one path to a table in a list of cascading referential actions.

You can enforce referential integrity in several ways. Declarative Referential Integrity (DRI) is the most basic way, but it is also the least flexible way. If you need more flexibility, but you still want a high degree of integrity, you can use triggers instead.
MORE INFORMATION
The following sample code is an example of a FOREIGN KEY creation attempt that generates the error message:
Use tempdbgocreate table table1 (user_ID integer not null primary key, user_namechar(50) not null)gocreate table table2 (author_ID integer not null primary key, author_namechar(50) not null, lastModifiedBy integer not null, addedby integer notnull)goalter table table2 add constraint fk_one foreign key (lastModifiedby)references table1 (user_ID) on delete cascade on update cascadegoalter table table2 add constraint fk_two foreign key (addedby)references table1(user_ID) on delete no action on update cascadego--this fails with the error because it provides a second cascading path to table2.alter table table2 add constraint fk_two foreign key (addedby)references table1 (user_ID) on delete no action on update no actiongo-- this works.				

REFERENCES

For more information, please see the "Cascading Referential Integrity Constraints" topic in SQL Server 2000 Books Online.
Properties

Article ID: 321843 - Last Review: 11/02/2007 10:14:14 - Revision: 3.4

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Enterprise Edition, Microsoft SQL Server 2000 Personal Edition, Microsoft SQL Server 2000 Desktop Engine (Windows), Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbbug kbpending kbprb kbprogramming KB321843
Feedback