Why does violation of PRIMARY KEY constraint return error code 2627 not 2601 in SQL Server?

Question

I am confused for a while since the Document point out:

When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values.

I have a table in SQL server with a PRIMARY KEY constraint. According to the above point,a unique clustered index on the column or columns is automatically created since i did't create any clustered in the table.

I learned 2601 Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls' from Database Engine Errors.

My question is that why SQL server return error code 2627 and not 2601 when I try to insert duplicate value in primary key column into my table which have a unique clustered index on primary key? Is it because 2627 has a higher priority than 2601 or what?

Can someone please give me some advice or help? Thanks.

My guess is because the object is marked as primary key, which seems to have a higher priority when determining the error code then the unique index — GuidoG

Larnu Larnu · Accepted Answer · 2019-07-25T08:55:02

A Primary Key, at least on SQL Server, is a type of Constraint. As a result when you create a Primary Key it is both a (unique) Index and a Constraint. Both error 2627 and 2601 have the same severity, so it appears that SQL Server will return the higher error code (as both a unique index and constraint were violated).

From testing, you'll only get the error 2601 is the column has a unique index that is violated, but does not have a constraint. Most likely, therefore, you'll see this on a conditional unique index.

Take the below examples:

USE Sandbox;
GO
--First sample table with primary key (Clustered)
CREATE TABLE dbo.TestTable1 (ID int PRIMARY KEY);
GO
--inserts fine
INSERT INTO dbo.TestTable1
VALUES(1);
GO
--Errors with code 2627
INSERT INTO dbo.TestTable1
VALUES(1);
GO
--Create second sample table, with unique Constraint
CREATE TABLE dbo.TestTable2(ID int,
                            CONSTRAINT U_ID UNIQUE(ID));
GO
--Inserts fine
INSERT INTO dbo.TestTable2
VALUES(1);
GO
--Errors with code 2627
INSERT INTO dbo.TestTable2
VALUES(1);
GO
--Create third sample table
CREATE TABLE dbo.TestTable3(ID int);
--Create unique index, without Constraint
CREATE UNIQUE INDEX ID_UX ON dbo.TestTable3(ID);
GO
--Inserts fine
INSERT INTO dbo.TestTable3
VALUES(1);
GO
--Errors with code 2601
INSERT INTO dbo.TestTable3
VALUES(1);
GO
--Clean up
DROP TABLE dbo.TestTable1
DROP TABLE dbo.TestTable2
DROP TABLE dbo.TestTable3

Note that only the last insert fails with error 2601; the other 2 fail with 2627.

Why does violation of PRIMARY KEY constraint return error code 2627 not 2601 in SQL Server?

1 Answers