SQL Insert Failing - Violation of Primary Key Cons

问题:

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?

回答1:

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>);