Violation of Primary Key Constraint Error Code 2627

Suppose, you want to insert some data in SQL Server database using an INSERT SELECT statement that contains a primary key, and when you run the statement you receive an error message

2627, Level 14, State 1
    Violation of PRIMARYKEY constraint ’PrimaryKey’ cannot insert duplicate key in object ‘TableName’.

Violation of primary key constraint source mssqlserver error number 2627 shows that, you have a primary key defined in the table which consists the relating fields to the values.

Reasons of SQL Server Violation of Primary Key Constraint Error Code

The error “violation of primary key constraint cannot insert duplicate key in object sql server” occurs because, there is a bug in the spool and spool is generating more than expected rows. It is possible that there might be a duplicate record in the table and duplicate record causes the error ‘can not insert duplicate key in object’.

Symptoms of Violation of Primary Key Constraint Error

1. It is possible that in INSERT_SELECT statement, the table that receiving the data is also used for the output data.
2. The referenced table is different in INSERT_SELECT statement and it includes a temporary table.

How to Solve Violation of Primary Key Constraint in SQL Server

To fix SQL Server Error 2627 Severity 14 State 1, you can use the Cumulative Update 3. It is recommended that you should use most recent cumulative update that contains the hotfix.

Or, you can perform the steps given below to resolve the issue related to the violation of primary key constraint.

1. Identify the duplicate records.

SELECT columns from primary key
    Customer_Name, Address,
    Customer_ID
    FROM
    Query to find duplicates from
    SELECT columns from primary key
    Customer_Name, Address,
    Customer_ID
    FROM Customer_Info
    UNION ALL
    SELECT ID
    FROM Customer_Info
    ) x
    GROUP BY - Columns List of primary key
    Customer_Name, Address,
    Customer_ID
    FROM Customer_Info
    HAVING COUNT(1) > 1

2. Once you have determined the duplicate records and found which record is valid and which one is not valid, you can manually delete the duplicate record using SQL Server Management Studio.

It is also possible that, after performing these steps the violation error of the primary key constraint may remain same. It happens only if the indexes were not created properly. In this situation you need a backup plan of the database to restore the data.

Note: Do not rename an existing table as a backup of tables as this will leave the indexes in place which will prevent the recreation of the indexes when the table is recreated.

Conclusion

In this article we have discussed about violation of primary key constraint source mssqlserver error number 2627. The article also describes the symptoms of error, reason for such kind of errors and how to fix violation of primary key constraint error.