How can reset primary key id after deleted the row.

Question

0.00/5 (No votes)

See more:

Hi friends,

I deleted the record from existing table which primary key column created with identity method. after deleting record when i enter the new record to table than it identity taken from the next value of last deleted.but i want to reset them from first value. how can i do it

Thanks in advance

Posted 25-Aug-12 7:23am

baskaran chellasamy

Add a Solution

3 solutions

Solution 3

While you can use DBCC to re-seed the primary key column value, I think this shouldn't be done. One characteristics of a surrogate key is that the key value itself is meaningless, it's just unique. This is exactly what identity does.

When a surrogate key is used, there is no quarantee that all the numbers are present (and there shouldn't be no quarentee).

For example if you have 100 rows and the kay values are from 1 to 100, consider what happens when record 78 is deleted. Now there's a hole in the sequence, but if you reseed the identity to 78 to get the missing value, the next inserts will fail since records 79, 80 and so on already exist.

Posted 25-Aug-12 8:21am

Wendelius

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)

Christian Graus · Accepted Answer · 2012-08-25T07:29:00

Solution 1

DBCC CHECKIDENT (MyTable, RESEED, 1) will reset the index on your table

Posted 25-Aug-12 7:29am

Christian Graus

Sarrrva · Accepted Answer · 2012-08-25T07:39:00

Solution 2

hi try this

SQL

The following line resets the Identity value for the table to 0 so that the next record added starts at 1.

DBCC CHECKIDENT('Tablename', RESEED, 0)

regards
sarva

Posted 25-Aug-12 7:39am

Sarrrva