How can I fix up this Identity int on my DB table?

Question

Hi folks,

A week or so ago some data got imported into a db table. Simple table:

Id INT PK NOT NULL IDENTITY Name VARCHAR(20)

Now, i noticed today that the first id starts at 0. wtf? asking the kid, he was reseeding it during his testing. So the first entry was a zero. Was an accident.

Anyways, I'm not sure how to best update the value. How can i push everything up one number? What pains me is that there's also some FK dependencies. If i have Cascade Update, should that fix the dependencies?

Also, should i update each number (how ever i do that??) from the top down .. eg. from the highest number. then +1. then go down.

hm.. i'm not sure :(

cheers :)

Answer 1

First of all, why are you stressing? ID=0 is complete valid, if a bit unusual. (Note that Zero Is NOT NULL)

If you really want to get rid of the 0:

• add a dummy record,
• note it's ID,
• If there are foreign keys pointing to Record 0, update them to the dummy record.
• delete that record. (to advance the internal ID counter).
• Just change ID 0 to that value.