Reset Identity Column in SQL Server

Question

I am making an SQL database in which contacts are stored. I want to be able to delete contacts, and the correct identifier for each contact is critical to connecting my software to it. Suppose I have contact with James, and he is the first. Its identifier is 0. I am adding Mary and her identifier is 1. If I remove James, how will Mary id be set to 0 instead of remaining 1? It should reset, since now it is the first. In other words, how can I reset all identifiers in the database when someone is deleted? Thanks

Answer 1

It makes no sense to do this in the primary key column with auto-increments, since even if it was trivial, without massive updates in linked tables, you affect data integrity. To do this, you probably have to abandon the index and primary key constraints from the column (at this point your application may wipe), renumber all subsequent records, renumber all related tables, and then reapply the primary key constraint and index.

If you really should have some kind of linear identifier that always starts with 0 (this may indicate a software design problem), then you can have an additional identification column in addition to the primary key, which is then updated to shuffle higher values step by step using an instruction, for example:

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 categorically reject this practice - if your identifiers are "missing" due to deleted records, the software should check for these values, and not just rewind them.

Answer 2

This is such a bad idea in many ways. I am discussing if I should show you how to do this. There should never be a reason to change the row identifier after setting it.

If you are probably using the wrong field as your PK id. I make the assumption here that you are talking about your PK field, which is also an identity column.

Keep in mind that if you create tables that link to your contact table and you start changing your identifier, you also need to update all of these tables. What will be expensive ...

Answer 3

This will be very slow if you have more than the trivial number of records in the database. The identity column will not work for you, you need to do some custom tsql to keep changing all the numbers - but a very bad idea, imo.

Why not use a date and time stamp if you need to keep track of how they were added.

You need to rethink your design.

Answer 4

This is not how IDs work, not how they should work. The identifier should never change, or all related information points to an invalid string.

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

Answer 5

An identifier is a unique identifier for a string.

It can be used to bind a row to another row in another table. The absence of an identifier also contains information in itself, as this clearly indicates that it was deleted. Starting with the reuse of identification numbers, the goal of having a unique identifier completely wins and makes no sense. When an identifier is assigned to a string, you should not arbitrarily change it.

Imagine when someone dies, they give their Social Security Number (ID) to someone else. This will lead to the transfer of all the old information related to the deceased's social security number to this new person, which makes no sense. The same thing happens with identifiers, if the identifier is reassigned, it will inherit any old data that was previously associated with it.

Answer 6

This would be much better solved using a different method, except to renumber the identifier column each time a row is deleted.

It's hard to say what else you would have done without knowing why your application has this need, but the fact that your application needs this feature probably indicates a design problem somewhere.

Answer 7

You use an identifier as more than just an identifier. In this case, you cannot use the auto-increment field. You will need to handle this in your code.

Answer 8

1 minute google search gave me a page that I can’t display. Google it, and it will be your first link from 6/1/2009: tsql fix "identifier column"

Essentially, I would suggest adding a foreign key constraint between all of your relational fields in the identifier field in question before doing any renumbering (which is also a terrible idea if there is any relationship, strictly because if you ask this question, you will have a hell of a time).

If your contact table is ONLY your table or has a ZERO relationship based on this identifier field, you can set the Identity property to NO, renumber values ​​from 1 to COUNT (ID), then set the Identity property to YES and re-fill the identifier to complete using:

DECLARE @MaxID INT

SELECT @MaxID = COUNT (ID) FROM TableID

CHECKIDENT DBCC ('TableID', RESEED, @MaxID)

In this case, you can use the above reseed script after each set of deletions (but change the value of COUNT (ID) to MAX (ID), as soon as everything is initially and correctly configured, this will add a bit of speed as the table grows), to any additional inserts or foreign key constraint restrictions. Make sure that you are using OPERATIONS wrapped around deletion and reuse blocks, and make sure the table only allows synchronous transactions, which will prevent any data hoses in the middle of repetitions.

Complex eh? That's why it's best to start with the right foot .;) (I have learned this from experience) Send me an email at mraarone et yahoo d0t com if you have any more questions.

Answer 9

I wrote an application to handle a multi-level sales program. Of course people quit. In ours, people also had to be inserted.

You are on the right track with one modification.

Identification number (ID) and serial number (seq) are two different things. They have nothing to do with each other.

Never change the identifier. After the appointment is always assigned.

Create a column (cNEXT) in your table for the sequence and fill it with identifiers. "Which identifier should be next in this sequence?"

Shuffle identifiers in cNEXT around by reassigning cNEXT at any time. Any saved proc can do this.

Then you also have the flexibility to create unclassified identifier chains. This is useful when people move to different regions or receive promotions for different groups.

Hope this helps! :)

Answer 10

All answers suggest that this is a production environment. If you are testing database design, then you want to quickly truncate all the tables, there should be an easy way to accomplish this:

DBCC CHECKIDENT ({table name}, reseed, 0)

* Remove all rows from all tables using the first identifier