Primary keys used for traversing table entries.

G'day!

I'm working against a MySQL table via PHP and now I've run into the problem
that when I remove entries, there are annoying holes for my(primary key
int(10), variable name "id". Does anyone know of a good way to re-sort the
entries in the database so that the integer id has a matching entry for each
value? I know this could be solved by simply traversing the entire table
using SQL commands and doing insertion sorts where ever I find "holes" (that
is, id steps >1, such as <this is an entry with id=587>, and <this is
another entry with id=589> ). But to do this every time one single table
entry is removed will use up too much processing time (all entries above the
"hole" will have to be reassigned a new id value).

Isn't there a good sql command that can be invoked via php that handles
this? Also, since the primary key (id) is incremented for each new value,
the same php command (or other solution) must also "reset" id to count from
<top value of last existing id>+1. As it is now, I don't know how to reset
the primary key counter and it goes on ad infinitum (or whatever limit is
coded into the rdbms).

Thanks for any tips!

Arty

On Mon, 8 Nov 2004 20:53:13 +0100, "A I" <a_incubo@yahoo.se> wrote:

>I'm working against a MySQL table via PHP and now I've run into the problem
>that when I remove entries, there are annoying holes for my(primary key
>int(10), variable name "id". Does anyone know of a good way to re-sort the
>entries in the database so that the integer id has a matching entry for eac
h
>value?

Why are the holes annoying? The primary key is the identity of an entity, wh
y
do you want to change the identity of all your entities? You'll also have to
change any child tables too. What's gained by this? You've got an int(10) - 
are
you really getting close to your 10,000,000,000th row?

You should not read any meaning into autogenerated primary keys; they are a
unique identifier, and that's it. The fact that they're usually ascending
numeric keys is just because that's easiest to generate; you shouldn't assum
e
they will be in order or gap-free, though.

Maybe you'd "pack" the IDs together as a rare maintenance operation if you'r
e
really getting close to the limit, but doing it per delete is severe overkil
l.
There's no single SQL command to do it in MySQL, because you'd have to
"cascade" the update to foreign keys. If it's just one standalone table with
 no
relationships to other tables, you could probably drop the primary key, and 
add
another one as auto_increment, which MySQL will automatically populate for t
he
existing rows. But since you've then just rewritten all the IDs, you'd then
have no idea which row is which any more.

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool