Reset the Primary Key

Recently, I had been insert some records into a table that has a foreign key as it's primary key. Why? I'm not sure, but it was set up by someone else and is now used company wide and would be a pain to change.

That said, as I put it the new records I tested a couple inside a transaction and then rolled them back to make a few changes. Then I input again and the primary key (really the foreign key for the other table) had a gap in the sequence and therefore wouldn't link up with the other table properly.

So I had to figure out how to remove the records I had just placed in said table and reset the index. There are two fairly easy solutions that I will outline below.

Solution 1
The first solution involves just using the query language to make the changes.

1. Remove any records that you need to.
2. You need to reset the primary key. Remember to use the number that is one below where you want your next ID to start. So if you want to start with 742, you would reseed with 741.

DBCC checkident ('TableNameGoesHere', reseed, 741) 


3. Then re-run your inserts and it should be all set.

Solution 2

The second solution uses the GUI.

1. Go to the table you want to modify and right click on it.
2. Click design.
3. Click the column you want to use and go down to properties.
4. Find Identity Specification.






5. Turn it to no.
6. Make your inserts, explicitly stating the ID.
7. Turn the identity specification back on.