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

Msg 8139, Level 16, State 0, Line 26

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

Let’s explore it.

Step 1:- Create Table1

CREATE TABLE Table1

(

       Id int identity,

       Name varchar(50)

)

Step 2:- Create Table2

CREATE TABLE Table2

(

       Id int,

       Name varchar(50)

)

Step 3:- Add Primary Key on Table1, column Id

ALTER TABLE Table1 ADD CONSTRAINT PK_Id PRIMARY KEY(Id)

Step 4:- Add Foreign Key on Table2, column Id i.e. references to Table1 column Id

ALTER TABLE Table2 ADD CONSTRAINT FK_Id FOREIGN KEY REFERENCES Table2(Id)

If you get the following error

Msg 8139, Level 16, State 0, Line 26

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

Change the query and specified the column name for foreign key constraints

ALTER TABLE Table2 ADD CONSTRAINT FK_Id FOREIGN KEY(Id) REFERENCES Table1(Id)

Here, Id is Table2 table column.

NOTE:

DROP the both table CONSTRAINT

ALTER TABLE TABLE2 DROP CONSTRAINT FK_Id

ALTER TABLE TABLE1 DROP CONSTRAINT PK_Id

Run the below query

ALTER TABLE Table2 ADD CONSTRAINT FK_Id FOREIGN KEY(Id) REFERENCES Table1(Id)

If you run the above query, you will get the following error

Msg 1776, Level 16, State 0, Line 46

There are no primary or candidate keys in the referenced table 'Table1' that match the referencing column list in the foreign key 'FK_Id'.

Msg 1750, Level 16, State 0, Line 46

Could not create constraint or index. See previous errors.

Error state that you are trying to create foreign key on table2 table but referenced table 'Table1' does not have primary key or candidate key on column Id

Drop table Table2, Table1

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table, it can also be defined to reference the columns of a UNIQUE constraint in another table.