問(wèn)題描述
我發(fā)現(xiàn) SQL Insert 語(yǔ)句有一個(gè)非常奇怪的問(wèn)題，我有一個(gè)簡(jiǎn)單的表，有一個(gè) ID 和 2 個(gè)日期時(shí)間，請(qǐng)參閱下面的創(chuàng)建腳本 -
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]
我現(xiàn)在正在嘗試運(yùn)行以下插入腳本 -
I am now trying to run the following insert script -
INSERT INTO [dbo].[DATA_POPULATION_LOGS]
([START]
,[FINISH])
VALUES
(GETDATE()
,GETDATE())
由于以下錯(cuò)誤而失敗 -
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).
每次執(zhí)行insert時(shí)，上面錯(cuò)誤信息中的重復(fù)鍵值都會(huì)增加，所以它似乎知道它是一個(gè)標(biāo)識(shí)列.
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.
是什么導(dǎo)致了這個(gè)問(wèn)題?！
What would be causing this issue?!
提前致謝.西蒙
編輯
我現(xiàn)在已經(jīng)創(chuàng)建了該表的副本，并且可以使用該腳本將其插入到新表中，可能導(dǎo)致它失敗的原因是什么?
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?
推薦答案
可能有人針對(duì)該表發(fā)出了 DBCC CHECKIDENT.當(dāng)您這樣做時(shí)，SQL Server 將服從您，并嘗試從 RESEED 開(kāi)始生成值并以增量遞增.它不會(huì)首先檢查這些值是否已經(jīng)存在(即使存在 PK).產(chǎn)生相同錯(cuò)誤的簡(jiǎn)單重現(xiàn):
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;
為了防止這種情況發(fā)生，你可以弄清楚現(xiàn)在的最大值是多少，然后再次運(yùn)行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>);
這篇關(guān)于SQL 插入失敗 - 違反主鍵約束的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！