Introducing FOREIGN KEY constraint 'Constraint Name' on table 'Table Name' may cause cycles or multiple cascade paths

Whenever we define FOREIGN KEY with CASCADE options for UPDATE/DELETE in more than one column on a table the following error occurred.

Scenario:
1. I have one Parent table named "Table1"

IF OBJECT_ID('Table1','U') IS NULL
Create Table Table1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
Column1 VARCHAR(10)
)
GO


2. I have one child table named "Table2"

IF OBJECT_ID('Table2','U') IS NULL
Create Table Table2
(
Id INT,
Column2 VARCHAR(10),
Id_2 INT
)
GO


3. Defining FOREIGN KEY with CASCADE option and reproducing the Error:
I just want to define FOREIGN KEY on Id and Id_2 columns with CASCADE option.

The following script will work fine

ALTER TABLE dbo.TABLE2
ADD CONSTRAINT Fk_Id FOREIGN KEY(Id)
REFERENCES dbo.Table1(Id) ON UPDATE CASCADE
GO


Command(s) completed successfully.

The following script fails with Error, This table already used the Key column "Table1(Id)" with CASCADE Option..
ALTER TABLE dbo.TABLE2
ADD CONSTRAINT Fk_Id_2 FOREIGN KEY(Id_2)
REFERENCES dbo.Table1(Id) ON UPDATE CASCADE
GO


Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'Fk_Id_2' 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 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.


Reason:
- The Key column "Table1(Id)" have been referred on "Table2(Id) and Table2(Id_2)" columns, So it throwing an Error.
- In such scenario, we can go for Trigger

No comments:

Post a Comment