問題描述
我發現 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>);
這篇關于SQL 插入失敗 - 違反主鍵約束的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！