Click here to Skip to main content
15,566,615 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a problem that whenever I delete a row, the row ID corresponding to that row gets deleted, but I don't want this. What I want is if any row is deleted, then other rows after that row should shift one (the no. of rows deleted) position up.

For Eg.

Suppose there is a user table(id and name)

id(auto incremented primary key) name
1 xyz
2 aaa
3 ray
4 mark
5 allen
now delete row with id=3 and table should look like

id(auto incremented primary key) name
1 xyz
2 aaa
3 mark
4 allen

Please help me out...

bbirajdar 13-Aug-12 6:04am    
Bad approach.. Its time to fire your Team Lead..

First of all: This is a bad idea..What about all the objects related to that ID? It's a relation database?
Solution: If you need to do this, you cant have the ID column as auto incremented primary key. You will have to set the id manually, then on delete update all rows with id higher than the deleted ID

UPDATE UserTable SET ID = ID-1 WHERE ID > @DeletedId

But why do you wan't to do this??

A better solution for you would be to add a new column that holds this number.
Don't mess up your primary keys...
Share this answer
Mehdi Gholam 13-Aug-12 5:53am    
Auto incrementing primary keys maintain the integrity of the database if you need incrementing codes, use your own column and maintain the increment yourself.
Share this answer
StianSandberg 13-Aug-12 5:55am    
Not a good idea at all.
The reason it is supposed to be unique is so that it should absolutely identify a record, not "temporarily identify a record".

Think about it: What if there are other tables which refer to this record? Unless you also update all of those, they will refer to the wrong record.
What if there are other users also accessing the database? What number are they supposed to use? The number they have loaded into memory and are about to update, or the number you just changed it to? How do they tell?

If you want a sequential number of records that changes as the records change, then add it as a different field, or use the ordinal number of the row in the table instead. Do not mess with Primary Keys - it only leads to trouble!

For example:
SELECT ROW_NUMBER() OVER (Order by InsertDate DESC) as RowNo, * FROM Tracks
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900