Reset Primary Key Column

Abolfazl

11y1.7k

i have a table named users which have a ID column as primary key(is identity=true).

when i add some data to table everything is ok but when delete some of them,

the deleted IDs cause Irregularity in ID column.i want to reset the ID column to begin from 1

and increment 1 per each data without remove existing data.

for example:

before delete : ID 1 2 3 4 5 6 7 8 9

after delete : ID 1 2 4 5 8 9

what should i do???

thanks

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi11y ago
No,

does not delete any record from the table. it just rest running identity value.

one more point you cannot update column value which define as Identity.

hope this will help you.
Abolfazl11y ago
thanks for your guidance.
i used DBCC CHECKIDENT but it delete all data in table, i don't want it.
i'm looking for a way that reset ID column without delete my data, maybe i should use a temp table and store my data before use DBCC CHECKIDENT as Rahul said.

Vikram Agrawal can you tell me how to update id column?

thanks
Jignesh Trivedi11y ago
Hi,
Update column value in identity column is not possible
Agree with Rahul solution, this is one way...
If Id column is foreign key for other table, truncate table is not work... in this case you can use DBCC CHECKIDENT command
check following link
https://msdn.microsoft.com/en-us/library/ms176057.aspx
hope this will help you.
Vikram Agrawal11y ago
Hi Abolfazl,
as per your requirement you need to update ID column after each deletion.
You need to write little logic for this in the Delte SP.

Thanks
Rizwan Ali11y ago
when you use the delete command then only table data is deleted not structure so id column always will be started again with the last value but when you use the truncate command then it will be start again from 1, and one thing is also noticeable that use identity(1,1) for increment of 1 every time when you add a new value in the table
Rahul Bansal11y ago
Always use the identity with primary key like this

create table table1
(
ID int PRIMARY key IDENTITY(1,1),
Name varchar(10)
)

In (1,1) , first 1 is starting value and second 1 means the incremental value.

To reset the identity column use

truncate table table1

which will delete all data of your table.

copy the data of the table into another table before truncate the table and then move the data.