Sql Server 2005 Primary Key violation on an Identity column

Question

Hi everyone, 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.

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)

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.

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

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.

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.

I really do appreciate the ideas folks.

Answer 1

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.

You may also have explicitly loaded/synched IDs too.

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

For more information, check out this page

Answer 3

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

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

But The cosmic ray is algo a good explanation ;)

Answer 5

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

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!

Can you tell that I hire interns every summer?

Answer 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

Answer 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

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

Answer 9

I forgot to set the indetity property Tanks Adam