Reset Identity column in SQL Server

Question

I am making an SQL database that stores contacts. I want to be able to delete contacts, and the correct id for each contact is crucial for my software connecting to it. Lets say I have contact James and he is the first one. His id is 0. I add Mary and her id is 1. If I delete James, how can Mary's id be set to 0 instead of staying 1? It has to reset since she is the first one now. In other words, how can I reset all of the IDs in the database when someone gets deleted? Thanks

Solution 1

It makes no sense to do this on an auto-incrementing primary key column, as even if it was trivial to do, without mass updates in related tables you affect your data integrity. To do so you would likely need to drop the index and primary key constraint from the column (at which point your application may flake out), renumber all later records, renumber all related tables, then re-apply the primary key constraint and index.

If you really must have some form of linear identifier which always starts at 0 (this may then indicate a problem with the software design) then you can have a secondary ID column in addition to the primary key, which you then update to shuffle higher values down a rung with a statement such as:

UPDATE table
SET secondaryID = secondaryID - 1
WHERE secondaryID > (SELECT secondaryID FROM table WHERE primaryID = [id to delete]);

DELETE FROM table
WHERE primaryID = [id to delete];

I strongly discourage such a practice - if your IDs are 'missing' values because of deleted records, the software should test for existence of these values rather than just wigging out.

Solution 2

This is such a bad idea in so many ways. I am debating if I should show you how to do this. There should never be a reason to change a row's identity once it's set.

If there is you are probably using the wrong field as your PK identifier. I am making an assumption here that you're talking about your PK field which is also an identity column.

Keep in mind if you create any tables which link to your contact table and you start changing your Id you need to update all those tables as well. Which will get expensive...

Solution 3

That's going to get real slow once you have more than a trivial amount of records in the database. The identity column will not work for you, you need to do some custom T-SQL to keep changing all the numbers - but a very bad idea, IMO.

Why not use a date/time stamp if you need to keep track of the order they were added?

You need to re-think your design.

Solution 4

That's not how IDs work, and not how they should work. The ID should never change, or all the linked information would point to the wrong row.

Instead, why not add a "External_ID" column that you control? Or number them dynamically in your query (with a computed column?)

Solution 5

The ID is the unique identifier of the row.

It can be used to link a row to another row in another table. The absence of ID holds information in itself as well, as it would clearly say that it was deleted. Starting to recycle ID numbers defeats completely the purpose of having a unique identifier, and doesn't make any sense really. Once an ID is assigned to a row, you must not arbitrarily change it.

Imagine for a second that when someone dies, they hand over his social insurance number (ID) to someone else. That will result in transferring all the old information that was linked to the dead person's social insurance number to that new person, which doesn't make any sense. Same happens with IDs, if an ID is reassigned, it'll be inheriting any old data that was previously linked to it.

View more solutions