CODEWITHSUNDEEP
sqlsql-servert-sql
user1948635
user1948635

Reputation: 1409

SQL Insert Failing - Violation of Primary Key Constraint

I am seeing a very strange issue with a SQL Insert statement, I have a simple table, with an ID and 2 datetimes, see create script below -

CREATE TABLE [dbo].[DATA_POPULATION_LOGS](
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [START] [datetime] NOT NULL,
    [FINISH] [datetime] NOT NULL,
 CONSTRAINT [PK__DATA_POP__3214EC2705D8E0BE] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I am now trying to run the following insert script -

INSERT INTO [dbo].[DATA_POPULATION_LOGS]
           ([START]
           ,[FINISH])
     VALUES
           (GETDATE()
           ,GETDATE())

It is failing with the following error -

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__DATA_POP__3214EC2705D8E0BE'. Cannot insert duplicate key in object 'dbo.DATA_POPULATION_LOGS'. The duplicate key value is (11).

The duplicate key value in the error message above increases every time the insert is executed, so it seems to know it is an identity column.

What would be causing this issue?!

Thanks in advance. Simon

EDIT

I have now created a copy of this table and can insert into the new table fine using that script, what could be causing it to fail?

Upvotes: 3

Views: 18211

Answers (1)

anon
anon

Reputation:

Probably someone issued DBCC CHECKIDENT against the table. When you do this, SQL Server will obey you, and try to generate values starting from the RESEED and incrementing by the increment. It doesn't check first to see if those values already exist (even if there is a PK). Simple repro that generates the same error:

USE tempdb;
GO
CREATE TABLE dbo.floob(ID INT IDENTITY(1,1) PRIMARY KEY);
GO
INSERT dbo.floob DEFAULT VALUES;
GO
DBCC CHECKIDENT('dbo.floob', RESEED, 0);
GO
INSERT dbo.floob DEFAULT VALUES;
GO
DROP TABLE dbo.floob;

To stop this from happening, you could figure out what the max value is now, and then run CHECKIDENT again:

DBCC CHECKIDENT('dbo.tablename', RESEED, <max value + 10 or 20 or something here>);

Upvotes: 9

Related Questions