标识列上的Sql Server 2005主键冲突

I’m running into an odd problem, and I need some help trying to figure it out.

我遇到了一个奇怪的问题,我需要一些帮助才能弄明白。

I have a database which has an ID column (defined as int not null, Identity, starts at 1, increments by 1) in addition to all the application data columns. The primary key for the table is the ID column, no other components.

我有一个数据库,除了所有的应用程序数据列之外,还有一个ID列(定义为int not null,Identity,从1开始,递增1)。表的主键是ID列,没有其他组件。

There is no set of data I can use as a "natural primary key" since the application has to allow for multiple submissions of the same data.

由于应用程序必须允许多次提交相同数据,因此没有可用作“自然主键”的数据集。

I have a stored procedure, which is the only way to add new records into the table (other than logging into the server directly as the db owner)

我有一个存储过程,这是将新记录添加到表中的唯一方法(除了直接以db所有者身份登录服务器)

While QA was testing the application this morning, they to enter a new record into the database (using the application as it was intended, and as they have been doing for the last two weeks) and encountered a primary key violation on this table.

虽然QA今天早上正在测试应用程序,但是他们要在数据库中输入一条新记录(按照预期使用应用程序,并且过去两周一直在进行),并在此表中遇到主键违规。

This is the same way I've been doing Primary Keys for about 10 years now, and have never run across this.

这与我十年来一直在做主键的方式相同,并且从未遇到过这种情况。

Any ideas on how to fix this? Or is this one of those cosmic ray glitches that shows up once in a long while.

有想法该怎么解决这个吗?或者这是长时间出现的宇宙射线故障之一。

Thanks for any advice you can give.

感谢您提出的任何建议。

Nigel

Edited at 1:15PM EDT June 12th, to give more information

编辑于6月12日美国东部时间下午1:15,以提供更多信息

A simplified version of the schema...

架构的简化版本......

CREATE TABLE [dbo].[tbl_Queries](
[QueryID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[Address] [varchar](150) NOT NULL,
[Apt#] [varchar](10) NOT NULL
    ... <12 other columns deleted for brevity>
[VersionCode] [timestamp] NOT NULL,
 CONSTRAINT [PK_tbl_Queries] PRIMARY KEY CLUSTERED 
(
    [QueryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

(also removed the default value statements)

(还删除了默认值语句)

The stored procedure is as follows

存储过程如下

insert into dbo.tbl_Queries
    (   FirstName,
        LastName,
        [Address],
        [Apt#]...) values
    (   @firstName,
        @lastName,
        @address,
        isnull(@apt, ''), ... )

It doesn't even look at the identity column, doesn't use IDENTITY, @@scope_identity or anything similar, it's just a file and forget.

它甚至没有查看标识列,不使用IDENTITY,@@ scope_identity或类似的东西,它只是一个文件而忘记了。

I am as confident as I can be that the identity value wasn't reset, and that no-one else is using direct database access to enter values. The only time in this project that identity insert is used is in the initial database deployment to setup specific values in lookup tables.

我尽可能地确信身份值没有被重置,并且没有其他人使用直接数据库访问来输入值。此项目中唯一使用标识插入的时间是在初始数据库部署中,以在查找表中设置特定值。

The QA team tried again right after getting the error, and was able to submit a query successfully, and they have been trying since then to reproduce it, and haven't succeeded so far.

QA团队在收到错误后立即再次尝试,并且能够成功提交查询,并且从那时起他们一直在尝试重现它,并且到目前为止还没有成功。

I really do appreciate the ideas folks.

我真的很欣赏这些想法。

8 个解决方案

#1

Sounds like the identity seed got corrupted or reset somehow. Easiest solution will be to reset the seed to the current max value of the identity column:

听起来像身份种子被破坏或以某种方式重置。最简单的解决方案是将种子重置为标识列的当前最大值:

DECLARE @nextid INT;
SET @nextid = (SELECT MAX([columnname]) FROM [tablename]);
DBCC CHECKIDENT ([tablename], RESEED, @nextid);

#2

While I don't have an explanation as to a potential cause, it is certinaly possible to change the seed value of an identity column. If the seed were lowered to where the next value would already exist in the table, then that could certainly cause what you're seeing. Try running DBCC CHECKIDENT (table_name) and see what it gives you.

虽然我没有关于潜在原因的解释,但是有可能更改标识列的种子值。如果将种子降低到表中已经存在下一个值的位置,那么这肯定会导致您所看到的。尝试运行DBCC CHECKIDENT(table_name)并查看它给你的内容。

For more information, check out this page

有关更多信息,请查看此页面

#3

Random thought based on experience

随机思考基于经验

Have you synched data with, say, Red Gate Data Compare. This has an option to reseed identity columns. It's caused issues for use. And another project last month.

您是否已将数据与Red Gate Data Compare同步。这可以选择重新设置标识列。它导致了使用问题。还有上个月的另一个项目。

You may also have explicitly loaded/synched IDs too.

您也可能已明确加载/同步ID。

#4

Maybe someone insert some records logging into the server directly using a new ID explicity, then when the identity auto increment field reach this number a primary key violation happened.

也许有人使用新的ID明确地插入一些记录到服务器的记录,然后当身份自动增量字段达到此数字时发生主键违规。

But The cosmic ray is algo a good explanation ;)

但宇宙射线是一个很好的解释;)

#5

Just to make very, very sure...you aren't using an IDENTITY_INSERT in your stored procedure are you? Some logic like this:

只是非常非常确定...你在存储过程中没有使用IDENTITY_INSERT吗?一些逻辑是这样的:

declare @id int;
Set @id=Select Max(IDColumn) From Sometable;
SET IDENTITY_INSERT dbo.SomeTable ON
Insert (IDColumn, ...others...) Values (@id+1, ...others...);
SET IDENTITY_INSERT dbo.SomeTable OFF
.
.
.

I feel sticky just typing it. But every once in awhile you run across folks that just never quite understood what an Identity column is all about and I want to make sure that this is ruled out. By the way: if this is the answer, I won't hold it against you if just delete the question and never admit that this was your problem!

键入它我感觉很粘。但每隔一段时间你就会碰到那些从未完全明白Identity列是什么的人,我想确保排除这一点。顺便说一句:如果这是答案,如果只是删除问题,我不会反对你,从不承认这是你的问题!

Can you tell that I hire interns every summer?

你能告诉我每年夏天都聘请实习生吗?

#6

Are you using functions like @@identity or scope_identity() in any of your procedures? if your table has triggers or multiple inserts you could be getting back the wrong identity value for the table you want

您是否在任何程序中使用@@ identity或scope_identity()等函数?如果您的表有触发器或多个插入,您可能会找回所需表的错误标识值

#7

Hopefully that is not the case, but there is a known bug in SQL 2005 with SCOPE_IDENTITY(): http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811

希望情况并非如此,但SQL 2005中存在一个已知错误,其中包含SCOPE_IDENTITY():http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx ?FeedbackID = 328811

#8

The Primary Key violation is not necessarily coming from that table.

主键违规不一定来自该表。

Does the application touch any other tables or call any other stored procedures for that function? Are there any triggers on the table? Or does the stored procedure itself use any other tables or stored procedures?

应用程序是否触摸任何其他表或调用该函数的任何其他存储过程?桌子上有没有触发器?或者存储过程本身是否使用任何其他表或存储过程?

In particular, an Auditing table or trigger could cause this.

特别是,审计表或触发器可能会导致此问题。

#1