Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : SQL Server Database Design - Internet Movie Database (IMDb)
Error Messages
Home > SQL Server Error Messages > Msg 2627 - Violation of PRIMARY KEY constraint Constraint Name.  Cannot insert duplicate key in object Table Name.
SQL Server Error Messages - Msg 2627 - Violation of PRIMARY KEY constraint Constraint Name.  Cannot insert duplicate key in object Table Name.

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 :