MYSQL , AUTO INCREMENT question.

[naiquevin]

Is it possible to have a mysql table in which the primary key 'id' is auto_incremented upon each new entry .. but shifts back if any record is deleted?
I mean suppose there are 10 records and 5th record is deleted, then the ids 6,7,8,9,10 adjust to 5,6,7,8,9..

IS this feasible and how ?
Thanks

[Demaestro]

You don't want to do that.

If my id is 10 then someone with an id of 9 is deleted my id shouldn't become 9. The whole point of an id is that it doesn't change.

Anyway to answer your question, no you can't do this with auto number, you would have to do it in application logic.

Is there a reason you want to do this? Perhaps we can help with a better solution if you explain your need to do this.

[naiquevin]

oops! .. that was indeed a bad idea ..
I figured out a better way to do it.
Thanks for replying

[Discovery]

auto increment is something like array based list, where as your problem is like pointers based list.

[edited by: Discovery at 7:17 pm (utc) on April 29, 2009]