CODEWITHSUNDEEP
mysql
vikram
vikram

Reputation: 9

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

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?

Upvotes: 0

Views: 1360

Answers (3)

Alexander Millar
Alexander Millar

Reputation: 176

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.

Upvotes: 0

cusimar9
cusimar9

Reputation: 5259

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.

Upvotes: 3

Konerak
Konerak

Reputation: 39763

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!

Upvotes: 18

Related Questions