0

I am not getting anywhere on Internet. I want to shift row up (change id of row) upon row deletion. Like:

 id        value
 1       edt55tgge
 2       edgg545re
 3       gt5edbeg4
 4       e98i7thde
 5       455t44ede

When I delete row 2 and 4, the table should become

 id        value
 1       edt55tgge
 2       gt5edbeg4
 3       455t44ede

I am using id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, where this becomes impossible. Please help.

user3111151
  • 57
  • 1
  • 1
  • 4
  • 1
    While using `AUTO_INCERMENT` this would be next to impossible. – Aziz Shaikh Dec 20 '13 at 10:27
  • `id` is your primary key - this means that all the other tables with FK's to this would have to somehow magically 'shift' as well. Not to mention the inconvenience to any other clients who were in possession of now-stale surrogate keys which are no longer idempotent. – StuartLC Dec 20 '13 at 10:38

2 Answers2

2

This isn't the way auto increment columns are meant to work, you really are best to leave it to sort itself out.

Add another INT column, called something like my_id and use that for what you want to do.

EDIT: Also you can't always guarantee that the auto increment value will be sequential, some cloud providers have their auto increment keys jump in 10's or 20's because of the mirroring and clustering etc... Best not to mess with them.

superphonic
  • 7,954
  • 6
  • 30
  • 63
  • You meanto say I should use just `INT NOT NULL PRIMARY KEY` – user3111151 Dec 20 '13 at 10:31
  • 2
    I think best practice is to have an auto incrementing primary key, just add an "additional" INT column, and manage that column yourself. Leave the auto increment column to do it's thing. – superphonic Dec 20 '13 at 10:33
0

You shouldn't amend auto increment. It's part of the database design. You should use another column name as the identifier. need to add another column.

if you using this in php then better to do some code functionality for the column (not auto_incremented). Thanks

uvais
  • 416
  • 2
  • 6