Showing posts with label Foreign Key. Show all posts
Showing posts with label Foreign Key. Show all posts

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

Number of referencing columns in foreign key differs from number of referenced columns, table 'TableName'.

When creating/adding referential integrity constraints on a Table, The following error occurred in various scenarios as given below..

Number of referencing columns in foreign key differs from number of referenced columns, table 'TableName'.

Meaning:
Column name not specified for the foreign key constraints.

When creating table:
IF OBJECT_ID('Tb_Table2','U') IS NULL
CREATE TABLE Tb_Table2
(
Id INT,
Column2 VARCHAR(10),
CONSTRAINT FK_ID FOREIGN KEY REFERENCES Tb_Table1(Id)
)
GO


or

Adding foreign key constraint on existing table:
ALTER TABLE Tb_Table2 ADD CONSTRAINT FK_ID FOREIGN KEY REFERENCES Tb_Table1(Id)
GO


Column name not specified for Foreign key constraints in both scripts

The script should be as follows...

IF OBJECT_ID('Tb_Table2','U') IS NULL
CREATE TABLE Tb_Table2
(
Id INT,
Column2 VARCHAR(10),
CONSTRAINT FK_ID FOREIGN KEY(Id) REFERENCES Tb_Table1(Id)
)
GO


or

ALTER TABLE Tb_Table2 ADD CONSTRAINT FK_ID FOREIGN KEY(Id) REFERENCES Tb_Table1(Id)
GO


Here, Id is the Parent table Key column.