index as 123..

[rulinus]

Hi,
I have a mysql db with a table that has a primary key that requires a strict 1,2,3,4,...,n+1 structure. The first thing i did was to assign that column with an AUTO_INCREMENT attribute. That seemed to work fine until the user erased one row (Its a web administred db). E.G.:there are 24 rows and then they deleted the last one. When adding a new record, mysql putts in 25 instead of 24 back again.
So i decided to generate the index numbers prior to the insertion. And I did a really stupid thing and counted the # of rows and added 1 more. So now if they erased the 20th record, i can't add a new one because there would be a duplicate key! This only works if i delete the last record.
My question is this: Is there a mysql function that allows me to automatically re-index the whole table once a record has been deleted? or do i have to write a php scrip to do that?

any help would be appreciated
rulox

[xblue]

as far as I know mysql autoincrement is not meant to behave this way, it would only start over when the table is deleted and created again, which is probably not what you want.

if you need to have the numbers in sequence (which means recalculation each time you delete something), the best way I can think of is to use a column which is not autoincrement, and maintain this yourself.

insert action: select highest number and add 1
delete action: delete record, set all records with higher id to id=id-1

[Sxooter]

Why?

why does the primary key need to be in strict 123 order with no holes?

Is it something that a better / different design of you application could get around?

I ask because the function of autoincrement columns is NOT to provide a 123 ordering with no holes.

further, deciding to maintain such requires CONSIDERABLE horsepower usage on your machines, and gains you little, except meeting this arbitrary requirement. Most the times I see this pop up, someone designed the system thinking that autoincrements would work with no holes, and made bad assumptions along the way they shouldn't have made.

Is there some way to rework you code to NOT require something like this? It's a data consistency nightware waiting to happen.

[rulinus]

thanks guys, you've given me some great ideas.
willl be working on it
rulox