Post reply

Identity skipping numbers when foreign key violate

  • August 28, 2003 at 11:59 am

    #82836

    I have table with an identity column. This table also has a foreign key constraint. When you insert into the table the identity column increments by 1 as it should. When you have an insert that fails do to a foreign key constraint then you insert successfully after that the identity column skips a number. Has anyone ever seen this before?

    Thanks,

    Alina

  • August 28, 2003 at 3:41 pm

    #471884

    Yes. Happens for me too. Not just for foreign keys and for other constraints as well.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • August 28, 2003 at 3:50 pm

    #471885

    SQL 2000 in general or only after sp3?

  • August 28, 2003 at 4:02 pm

    #471886

    Dunno. All my SQL2000 is at SP3.

    But, it happens on SQL7.0 too.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • August 28, 2003 at 4:29 pm

    #471888

    ...but this opens up a wonderful way of restricting the use of a certain range of IDENTITY values. Take for instance:

     

    create table MyTable

    (id int identity,

    name sysname,

    constraint SillyConstraint check (ident_current('MyTable')+1 not in (3,4,5)))

    

    insert MyTable select 'smith'

    insert MyTable select 'jones'

    insert MyTable select 'brown'

    insert MyTable select 'black'

    insert MyTable select 'white'

    insert MyTable select 'green'

    insert MyTable select 'mccoy'

    insert MyTable select 'james'

    

    select * from MyTable

    This will prevent rows being inserted with IDENTITY values of 3 to 5.

    This is a great solution! Now, as usual, I only need a problem to apply it to.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • August 29, 2003 at 1:04 am

    #471926

    identity just generates a (sequenced) number, but it does not generate it by recalculating it using the table itself, but it keeps it in a systemtable. It should be no problem if numbers are "skipped" because of insert-errors or deletes.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • August 29, 2003 at 1:12 am

    #471927

    Hi Alina,

    quote:

    I have table with an identity column. This table also has a foreign key constraint. When you insert into the table the identity column increments by 1 as it should. When you have an insert that fails do to a foreign key constraint then you insert successfully after that the identity column skips a number. Has anyone ever seen this before?

    as alzdba said, this is normal behaviour.

    IDENTITY's purpose is not to generate a consecutive numbering, but to provide a unique value for the column. Something like autonumber in Access.

    If you need that consecutive numbering you have to implement this by yourself

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • August 29, 2003 at 6:18 am

    #471964

    Thank you all for your input.

  • August 30, 2003 at 3:08 am

    #472043

    Its better to use the INSERT statment between

    BEGIN and COMMIT/ ROLLBACK, in which case I think you will not face this kind of issue. Or Another way is, to formulate the running serial number than having a IDENTITY field. Which case u will have more control over the transaction. Hope this help.

  • September 2, 2003 at 12:04 pm

    #472238

    The identity increment can't be easily undone because another connection doing inserts into the same table might be affected.

    A COMMIT or ROLLBACK will not affect the next identity value to be used (either on yours or another connection).

    There is a DBCC CHECKIDENT command that can check or set the identity, if something must be fixed.

    Edited by - rstone on 09/02/2003 12:17:13 PM

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Viewing 10 posts - 1 through 9 (of 9 total)

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