PHP SQL - Primary keys used for traversing table entries.

Author

Primary keys used for traversing table entries.

A I

2004-11-08, 3:59 pm

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

Andy Hassall

2004-11-08, 3:59 pm

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 each
>value?

Why are the holes annoying? The primary key is the identity of an entity, why
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 assume
they will be in order or gap-free, though.

Maybe you'd "pack" the IDs together as a rare maintenance operation if you're
really getting close to the limit, but doing it per delete is severe overkill.
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 the
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

Sponsored Links