SQL Server Error 1785

Why it happens

Error 1785 is raised at CREATE TABLE or ALTER TABLE time, not when you delete data. You asked SQL Server to add a FOREIGN KEY with ON DELETE CASCADE or ON UPDATE CASCADE, and that new cascade would combine with an existing one so that the same table could be reached by two different cascade paths, or by a loop back to itself.

When more than one cascade path points at a table, the order in which rows would be deleted or updated is ambiguous. SQL Server refuses to define a constraint whose runtime behaviour it cannot make deterministic, so it rejects the statement up front rather than risk inconsistent cascading later. The message names the constraint and the table that triggered the check.

This is severity 16, a normal design-time error you can fix by changing the constraint definition. It commonly appears when two tables both reference a shared parent with CASCADE, or when a chain of cascading relationships eventually forms a cycle.

Examples

Two cascade paths reaching the same table

CREATE TABLE dbo.Customers (
    CustomerID INT PRIMARY KEY
);

CREATE TABLE dbo.Orders (
    OrderID    INT PRIMARY KEY,
    CustomerID INT REFERENCES dbo.Customers(CustomerID) ON DELETE CASCADE
);

CREATE TABLE dbo.OrderItems (
    OrderItemID INT PRIMARY KEY,
    OrderID     INT,
    CustomerID  INT,
    CONSTRAINT FK_OrderItems_Customers
        FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID) ON DELETE CASCADE,
    CONSTRAINT FK_OrderItems_Orders
        FOREIGN KEY (OrderID) REFERENCES dbo.Orders(OrderID) ON DELETE CASCADE
);

Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'FK_OrderItems_Orders' on table 'OrderItems' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Adding the cascading constraint with ALTER TABLE

-- OrderItems already cascades to Customers.
-- Adding a second cascade path through Orders fails.
ALTER TABLE dbo.OrderItems
    ADD CONSTRAINT FK_OrderItems_Orders
    FOREIGN KEY (OrderID) REFERENCES dbo.Orders(OrderID) ON DELETE CASCADE;

Msg 1785, Level 16, State 0, Line 3
Introducing FOREIGN KEY constraint 'FK_OrderItems_Orders' on table 'OrderItems' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

A self-referencing cycle

CREATE TABLE dbo.Employees (
    EmployeeID INT PRIMARY KEY,
    ManagerID  INT,
    CONSTRAINT FK_Employees_Manager
        FOREIGN KEY (ManagerID) REFERENCES dbo.Employees(EmployeeID) ON DELETE CASCADE
);

Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'FK_Employees_Manager' on table 'Employees' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

How to fix it

CREATE TABLE dbo.OrderItems (
    OrderItemID INT PRIMARY KEY,
    OrderID     INT,
    CustomerID  INT,
    CONSTRAINT FK_OrderItems_Customers
        FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID) ON DELETE NO ACTION,
    CONSTRAINT FK_OrderItems_Orders
        FOREIGN KEY (OrderID) REFERENCES dbo.Orders(OrderID) ON DELETE CASCADE
);

-- Handle the second path explicitly when removing a customer
BEGIN TRANSACTION;
    DELETE FROM dbo.OrderItems WHERE CustomerID = @CustomerID;
    DELETE FROM dbo.Orders     WHERE CustomerID = @CustomerID;
    DELETE FROM dbo.Customers  WHERE CustomerID = @CustomerID;
COMMIT;

CREATE TRIGGER trg_Customers_Delete
ON dbo.Customers
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE oi FROM dbo.OrderItems oi
        JOIN deleted d ON oi.CustomerID = d.CustomerID;
    DELETE o FROM dbo.Orders o
        JOIN deleted d ON o.CustomerID = d.CustomerID;
    DELETE c FROM dbo.Customers c
        JOIN deleted d ON c.CustomerID = d.CustomerID;
END;

CREATE TABLE dbo.OrderItems (
    OrderItemID INT PRIMARY KEY,
    OrderID     INT,
    CONSTRAINT FK_OrderItems_Orders
        FOREIGN KEY (OrderID) REFERENCES dbo.Orders(OrderID) ON DELETE CASCADE
);

-- Reach the customer through the order when needed
-- SELECT o.CustomerID FROM dbo.Orders o WHERE o.OrderID = @OrderID;

How to prevent it

Map out every cascade path before you add ON DELETE CASCADE or ON UPDATE CASCADE. If a table can be reached from a common parent by two routes, or a relationship loops back on itself, keep at most one route as CASCADE and make the rest NO ACTION.

Prefer explicit deletes in a stored procedure or trigger over stacking multiple cascades. This keeps deletion order deterministic and easy to reason about. For background on how these relationships work, see primary key vs foreign key and the constraints guide.

Common questions