Update auto incremented primary key (ID)

Question

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...

Thanks

Posted 12-Aug-12 23:41pm

ritesh88sharma

Add a Solution

Comments

bbirajdar 13-Aug-12 6:04am

Bad approach.. Its time to fire your Team Lead..

3 solutions

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)

StianSandberg · Answer 1 · 2012-08-12T23:50:00

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

SQL

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...

Mehdi Gholam · Answer 2 · 2012-08-12T23:52:00

Solution 3

Auto incrementing primary keys maintain the integrity of the database if you need incrementing codes, use your own column and maintain the increment yourself.

Posted 12-Aug-12 23:52pm

Mehdi Gholam

Comments

StianSandberg 13-Aug-12 5:55am

5'd

OriginalGriff · Answer 3 · 2012-08-12T23:54:00

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:

SQL

SELECT ROW_NUMBER() OVER (Order by InsertDate DESC) as RowNo, * FROM Tracks

Update auto incremented primary key (ID)

3 solutions

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0