SQL Server Error Messages - Msg 2627

Error Message

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint Constraint Name.
Cannot insert duplicate key in object Table Name.

Causes:

This error occurs when you are trying to insert a new record into a table that contains a PRIMARY KEY constraint and the key of the record being inserted already exists in the table. As an example, suppose you have a table containing the different loan types that your application are accepting:

CREATE TABLE [dbo].[Loan Type] (
[Loan Type ID]  VARCHAR(20) NOT NULL PRIMARY KEY,
[Name]          VARCHAR(50) NOT NULL
)
GO

And your table already contains the following loan types:

Loan Type ID  Name
------------- ------------------
CAR           Car Loan
HOME          Home Loan
HOME EQUITY   Home Equity Loan
PERSONAL      Personal Loan
STUDENT       Student Loan

If you try to add another loan type where the Loan Type ID already exists without knowing that it is already in the table, you will get the error.

INSERT INTO [dbo].[Loan Type] ( [Loan Type ID], [Name] )
VALUES ('HOME EQUITY', 'Home Equity Loan')
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Loan_Type'.
Cannot insert duplicate key in object 'Loan Type'.
The statement has been terminated.

Solution / Work Around:

To avoid this error, check first if a record with a given key already exists in the table and if it doesn’t exist yet, then perform the INSERT:

IF NOT EXISTS (SELECT 'X' FROM [dbo].[Loan Type]
               WHERE [Loan Type ID] = 'HOME EQUITY')
    INSERT INTO [dbo].[Loan Type] ( [Loan Type ID], [Name] )
    VALUES ('HOME EQUITY', 'Home Equity Loan')

Related Articles

• Error Message 213

  ---

  Insert Error: Column name or number of supplied values does not match table definition.

• Error Message 245

  ---

  Syntax error converting the varchar value to a column of data type int.