Identity value Reset

  • Hi All,

    I have a question regarding resetting the identity column. If i have 5 rows in a table and the identity values for these rows are 1,2,3,4,5 and i delete the row with id 3, the output will be 1,2,4,5. Is there anyway i can reset these rows as 1,2,3,4

    Thanks!

  • No this is not possible. You can manually update the Ids though (identity_insert=on)

  • Changing the ID of a row can be a dangerous thing so I have a few questions first.

    What's your goal in doing this? Why does it matter if the IDs are sequential or not? Are there any foreign key constraints referencing this field in other tables?

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • This is not the most elegant way, but it should work. Export all data to a temp table (identical structure as the original including identity field). Then delete the data from the original table, set Indentity_Insert on (enable Identity Insert in the Import Wizard) and insert the data from the temp into the original and set Identity_Insert off.

    or if you are talking about that few records you could do the following

    Alter the column in the table removing the identity setting. Make your changes and then set the column back to identity.

  • Thanks Viiki and Don I got my answers. This is just for my theory purpose.

  • Don Bricker - Illinois (11/1/2011)


    This is not the most elegant way, but it should work. Export all data to a temp table (identical structure as the original including identity field). Then delete the data from the original table, set Indentity_Insert on (enable Identity Insert in the Import Wizard) and insert the data from the temp into the original and set Identity_Insert off.

    can't see how this is going to help, it will just re insert the data back as it was unless you make changes to the data first when it's in the temp table. You would also need to truncate the original table to reset the seed. This still raises the issue of foreign key constraints. I prefer Viikis approach of setting identity insert on and correct the record in the original table, you must then also correct tables with any foreign keys too

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thanks Perry! That was useful information.

  • Use INSTEAD OF trigger for DELETE

    1.DELETE N identity row (in your example 3).

    2.UPDATE the identity rows more than N

    3.UPDATE FKs as well. (if any)

    Or

    3.Can be defined the FK definition as well.

    ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

Viewing 8 posts - 1 through 7 (of 7 total)

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