Post reply

IDENTITY problem on Primary Key column in a table

  • July 26, 2009 at 1:51 pm

    #208784

    Users are reporting failures to insert rows in a table. The table is little used and was thought to be working well until recently. On investigation, I find that the max. Primary Key of the table is 145. The PK column has an IDENTITY set up, with SEED = 1 and INCREMENT = 1. The Insert statement is trying to insert a row with PK = 128 and failing. SELECT IDENT_CURRENT('TableName') returns 127.

    Does anyone know a quick and easy way out of this mess?

    Thanks in advance.

    Mark Thornton

  • July 26, 2009 at 2:08 pm

    #1030312

    DBCC CHECKIDENT() with RESEED option.

    N 56°04'39.16"
    E 12°55'05.25"

  • July 26, 2009 at 2:13 pm

    #1030315

    fastest way to fix it is to reseed the value with the next highest value...146 automatically:

    dbcc checkident(identicheck,RESEED) --sets to max

    i thought that could not happen, but sure enough, this example resets the value to 1, and would eventaully have an error on inserting #3 a second time.

    create table identicheck( checkid int identity(1,1) primary key, checktext varchar(20) )

    insert into identicheck(checktext) SELECT 'one'

    set identity_insert identicheck on

    insert into identicheck(checkid,checktext) SELECT 3,'three'

    set identity_insert identicheck off

    dbcc checkident(identicheck) --returns 3

    dbcc checkident(identicheck,RESEED,1) --sets to 1

    insert into identicheck(checktext) SELECT 'next' --inserts the value 2

    insert into identicheck(checktext) SELECT 'next' --should insert the value 3, crashes

    select * from identicheck

    drop table identicheck

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply