SQL Server Error Messages - Msg 1778

Error Message

Server: Msg 1778, Level 16, State 1, Line 1
Column 'Primary Table.Primary Key' is not the same data type as referencing column 'Referencing Table.Referencing  Column' in foreign key 'Foreign Key Constraint Name'.

Causes:

This error occurs when you are trying to create a foreign key relationship between two tables and the data type of the referencing column is different from the data type of the primary key.

To illustrate, here’s a script that will generate the error:

CREATE TABLE [dbo].[Employees] (
[ID]        VARCHAR(10)    PRIMARY KEY,
[Name]      VARCHAR(50),
[Position]  VARCHAR(2)
)
GO

CREATE TABLE [dbo].[EmployeeManager] (
[ManagerID]  INT REFERENCES [dbo].[Employees] ( [ID] ),
[EmployeeID] INT REFERENCES [dbo].[Employees] ( [ID] )
)
GO

Executing this script will generate the following error:

Server: Msg 1778, Level 16, State 1, Line 1
Column 'dbo.Employees.ID' is not the same data type as referencing column 'EmployeeManager.ManagerID' in foreign key 'FK__EmployeeM__Manag__31B762FC'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Solution / Work Around:

To avoid this error, always make sure that the data type of the referencing column is the same as the data type of the primary key being referenced. The following script will solve the problem:

CREATE TABLE [dbo].[Employees] (
[ID]        VARCHAR(10)    PRIMARY KEY,
[Name]      VARCHAR(50),
[Position]  VARCHAR(2)
)
GO

CREATE TABLE [dbo].[EmployeeManager] (
[ManagerID]    VARCHAR(10) REFERENCES [dbo].[Employees] ( [ID] ),
[EmployeeID]   VARCHAR(10) REFERENCES [dbo].[Employees] ( [ID] )
)
GO