SQL 插入失败 - 违反主键约束

2021-09-10 00:00:00 sql tsql sql-server

我发现 SQL Insert 语句有一个非常奇怪的问题，我有一个简单的表，有一个 ID 和 2 个日期时间，请参阅下面的创建脚本 -

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

每次执行insert时，上面错误信息中的重复键值都会增加，所以它似乎知道它是一个标识列.

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?!

提前致谢.西蒙

编辑

我现在已经创建了该表的副本，并且可以使用该脚本将其插入到新表中，可能导致它失败的原因是什么?

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?

推荐答案

可能有人针对该表发出了 DBCC CHECKIDENT.当您这样做时，SQL Server 将服从您，并尝试从 RESEED 开始生成值并以增量递增.它不会首先检查这些值是否已经存在(即使存在 PK).产生相同错误的简单重现:

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;

为了防止这种情况发生，你可以弄清楚现在的最大值是多少，然后再次运行CHECKIDENT:

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

相关文章