16. 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'.
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.
View Error Message Details and Solution / Workaround
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/Workaround:
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
17. The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
Error Message:
Server: Msg 120, Level 15, State 1, Line 4
The select list for the INSERT statement contains fewer items than the insert list.
The number of SELECT values must match the number of INSERT columns.
Causes:
As the error message describes, this error occurs when doing an INSERT to a
table using the INSERT INTO … SELECT FROM format and the number of columns
specified in the SELECT clause is less than the number of columns specified in
the INSERT clause.
View Error Message Details and Solution / Workaround
To illustrate, the following INSERT statement will generate the error:
INSERT INTO [dbo].[Employees] ( [FirstName], [LastName], [Gender] )
SELECT [FirstName], [LastName]
FROM [dbo].[Applicants]
Solution/Workaround:
To avoid this error, make sure that the number of columns specified in the
SELECT clause matches the number of columns specified in the INSERT INTO
clause:
INSERT INTO [dbo].[Employees] ( [FirstName], [LastName], [Gender] )
SELECT [FirstName], [LastName], [Gender]
FROM [dbo].[Applicants]
18. The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.
Error Message:
Server: Msg 121, Level 15, State 1, Line 4
The select list for the INSERT statement contains more items than the insert list.
The number of SELECT values must match the number of INSERT columns.
Causes:
As the error message describes, this error occurs when doing an INSERT to a
table using the INSERT INTO … SELECT FROM format and the number of columns
specified in the SELECT clause is more than the number of columns specified in
the INSERT clause.
View Error Message Details and Solution / Workaround
To illustrate, the following INSERT statement will generate the error:
INSERT INTO [dbo].[Employees] ( [FirstName], [LastName] )
SELECT [FirstName], [LastName], [Gender]
FROM [dbo].[Applicants]
Solution/Workaround:
To avoid this error, make sure that the number of columns specified in the
SELECT clause matches the number of columns specified in the INSERT INTO
clause:
INSERT INTO [dbo].[Employees] ( [FirstName], [LastName], [Gender] )
SELECT [FirstName], [LastName], [Gender]
FROM [dbo].[Applicants]
19. View or function 'View or Function Name' has more column names specified than columns defined. Could not use view or function 'View or Function Name' because of binding errors.
Error Message:
Server: Msg 4502, Level 16, State 1, Procedure Object Name, Line 4
View or function 'View or Function Name' has more column names specified than columns defined.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'View or Function Name' because of binding errors.
Causes:
This error occurs when a column being referenced by a view has been dropped from
the base table being referenced by the view and a SELECT statement is performed
on the view.
View Error Message Details and Solution / Workaround
To illustrate, suppose you have the following table and view:
CREATE TABLE [dbo].[Customers] (
[CustomerID] INT,
[CustomerName] VARCHAR(100),
[Address] VARCHAR(100),
[Gender] CHAR(1)
)
GO
CREATE VIEW [dbo].[CustomersView]
AS
SELECT * FROM [dbo].[Customers]
GO
Doing a SELECT * FROM [dbo].[CustomersView] statement will give you the rows
from the table with all the columns of the [dbo].[Customers] table.
The error above will be generated if one of the columns in [dbo].[Customers] is
suddenly dropped from the table and a SELECT statement is performed on the
view:
ALTER TABLE [dbo].[Customers] DROP COLUMN [Gender]
SELECT * FROM [dbo].[CustomersView]
Solution/Workaround:
To avoid this error, you have to refresh the view by calling the sp_refreshview
system stored procedure:
EXECUTE sp_refreshview '[dbo].[CustomersView]'
The sp_refreshview system stored procedure refreshes the metadata for the
specified view as persistent metadata for a view can become outdated because of
changes to the underlying objects upon which the view depends.
20. Invalid operator for data type. Operator equals add, type equals text.
Error Message:
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
Causes:
This error occurs when you are trying to concatenate 2 columns of TEXT data
type.
View Error Message Details and Solution / Workaround
Error Message:
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
To illustrate, assume that you have the following table definition:
CREATE TABLE [dbo].[StudentComments] (
[StudentID] INT,
[CommentsOnTeacher] TEXT,
[CommentsOnSubject] TEXT
)
The following SELECT statement will generate the error:
SELECT [StudentID],
[CommentsOnTeacher] + [CommentsOnSubject] AS [AllComments]
FROM [dbo].[StudentComments]
Server: Msg 403, Level 16, State 1, Line 3
Invalid operator for data type. Operator equals add, type equals text.
Solution/Workaround:
To work around this error you need to CAST the TEXT column into VARCHAR first
before concatenating the columns. The following query will avoid this
error:
SELECT [StudentID],
CAST(CAST([CommentsOnTeacher] AS VARCHAR(8000)) +
CAST([CommentsOnSubject] AS VARCHAR(8000)) AS TEXT)
AS [AllComments]
FROM [dbo].[StudentComments]
The only drawback with this work around is that you are limiting both TEXT
columns to 8000 characters each. One other way to work around this is to
do the concatenation in the client application instead of having SQL Server do
the concatenation.
|