Ressetting an auto increment primary key?

Question

0.00/5 (No votes)

See more:

Hi Guys.

Something has been bothering me for a while, i have been rigorously testing my database with updating, editing, and deleting.

one thing i have noticed while deleting is that the auto increment frunction on the primary keys do not revert when deleting from the datagrid.

for example:

if i have 5 entries in a table (id key 1, 2, 3, 4, 5) and delete the last entry (5), when i add back into that table it is still counting so the ids are (id key 1, 2, 3, 4, 6).

is there an easy way to ensure that the id keys count correcty instead of ++'ing regardless of deletion of data?

has it something to do with the fields under Identity Specification on the table definitions?

sorry if im not to clear on what im asking as its the only way i can think of explaining it....

Regards
Matt.

Posted 16-Apr-12 11:06am

BBCokeley

Add a Solution

2 solutions

Solution 1

You can reset the count by using this in SSMS

XML

DBCC CHECKIDENT (<tablename>, RESEED, <newvalue>)

Replace the tablename with the name of the table that you want to reset and replace newvalue with the number you want to start with - 1. For example if you want to start numbering back at 5 then the seed value will be 4.

Posted 16-Apr-12 11:12am

Wes Aday

Updated 16-Apr-12 11:16am

v2

Comments

BBCokeley 16-Apr-12 17:15pm

sorry i forgot to mention the table is not updated in the SSMS (detached from SSMS and copied to local), it is kep localy inside the application and reads/writes to this data base.

is there a way to do it from here? can i re atach the DB in its updated form?

thanks for helping.

[no name] 16-Apr-12 17:18pm

Yes you can re-attach the db within SSMS. Right click on Databases and Attach.

BBCokeley 16-Apr-12 17:28pm

ok trying it out now, will let you know
thanks

BBCokeley 16-Apr-12 17:40pm

Doesnt seem to be working, and the path im re attaching from is not the path that appears in the databases when i reatach it...

something is going wrong somewhere.

BBCokeley 16-Apr-12 17:45pm

On the DBCC CHECKIDENT (<tablename>, RESEED, <newvalue>) it runs the query and executes with no problems, but it doesnt seem to be ressetting it, as i ve checked the top 200 rows and still the huge leaps in incrementation remain :S.

any idea why?

BBCokeley 16-Apr-12 17:49pm

so i cant physically change the current numbers in the table?

only reset the increment for new data entries?

[no name] 16-Apr-12 18:31pm

I do not believe you can. I could be wrong. All I have been able to find is resetting the count not resetting everything else. Makes sense though. If you already had records matching up with those keys then other tables would have to be reset too.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BBCokeley · Accepted Answer · 2012-04-16T11:57:00

Right, i get what your saying, ive reattached the DB to SSMS, ive droped the primary key from the table manually reset the ID's then antered your helpful SQL statement and set it to continue on the value.

reset the primary key, and indexing back on.

detached and re imported to test and it works

Thanks for your help guys much appreciated :)

Best Regards
Matt.