How can I change auto increment primary key values on row deletion?

Question

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.

Example:

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

Is there any way to accomplish this?

Don't do this. You will lost parent-child table relationship — Shakti Singh, May 25 '11 at 07:55
Have you thought what would happen to records in other table that refers to this table's primary key column? For example for a record in another table refers to 4-mark, what happen when it becomes 3-mark? — Lukman, May 25 '11 at 07:56
possible duplicate of [MySQL - Auto Increment after delete](http://stackoverflow.com/questions/2214141/mysql-auto-increment-after-delete) — Aziz Shaikh, Dec 20 '13 at 10:33

score 18 · Answer 1 · edited Jun 20 '20 at 09:12

No! Don't do this!

Your Autoincrement ID is the IDENTITY of a row. Other tables use this ID to refer to a certain row. If you update the ID, you would have to update all other tables referencing this row, which is not at all the point of a relational database.

Furthermore, there never is a need to do this: you won't run out of autoincrement columns fast (and if you do, just pick a bigger datatype).

An autoincrement ID is a purely technical number, your application users should never see or use it. If you want to display an identificator to your users, add another column!

score 3 · Answer 2 · answered May 25 '11 at 07:56

3

You've completely got the wrong end of the stick. Auto numbers should not be changed as this would break the link between any other referencing tables.

What you want, by the sounds of it, is a row counter, not a primary key.

answered May 25 '11 at 07:56

cusimar9

5,185
4
24
30

score 0 · Answer 3 · answered Jun 21 '13 at 20:17

While its generally not recommended to change these values, there do exists instances where you may need to change them. If you have the appropriate Foreign Key relationships setup to cascade on UPDATE then you could do this. Granted you need to be 100% all FK relationships are defined as expected.

How can I change auto increment primary key values on row deletion?

3 Answers3

No! Don't do this!

Linked