Updating item numbers (or IDs)

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
After a while of deleting records in a MySQL db, there gets to be the
gaps in the id numbering system.  i.e. 1, 2, 3, 6, 7, 12, and so on.
Is there a way to renumber the id system in a table for 1, 2, 3, 4, 5,
6, etc without manually going in and changing those numbers?

Re: Updating item numbers (or IDs)

cover wrote:

Quoted text here. Click to load it

I hope not.
Why do you want such a strange thing?

While designing a database, good designers make use of foreign keys to to  
make sure the relations in the database will stay consistent.

Consider the following pseudocode:

create table tbluser(
 userid autonumber Primary Key,
 username text

create table writtenarticles(
  writtenarticleid autonumber Primary Key,
  writtenby numeric references tbluser(userid),
  title text,
  article text

The above situation makes sure that the value for writtenby in  
tblwrittenarticles exists in column userid in table userid.

If you decide to change the numbers in tbluser, this relation will be broken  
(and fires a exception/error in most cases.)

However, in some databases you have the option to cascade a change through  
all related tables via their foreign keys.

Bottomline: Such an autonumber/serial field is ment to uniquely point to a  
certain row, which is handy in a relational database.
It is not ment as a counter...

If you need such a counter, just do it programmatically, like looping over  
your resultset (ordered by userid eg), and increase your own counter.

I don't want to lecture you, but I think what you asking for is conceptually  
wrong. :-)

Good luck.

Erwin Moller

Re: Updating item numbers (or IDs)

On Wed, 26 Apr 2006 22:24:23 -0700, cover wrote:
Quoted text here. Click to load it

While I generally agree with Erwin on this, it's probably best leave them
alone, I do appreciate there may be a reason for it so...  

The only way to do it AFAIK in MySQL (pre version 5 at least) is to loop.
There's no "non-manual" way. In pseudo-code:

$handle1 = open_mysql_connection_and_db();
$handle2 = open_mysql_connection_and_db();

$result1 = send_mysql_query("SELECT ID FROM Table1", $handle1)
while ($row = get_mysql_row($result1)) {
    if ($row[ID] != $ID) {
        send_mysql_query("UPDATE Table1 SET ID='$ID' WHERE ID='$row[ID]'",

It's not pleasant and you'll need to trigger it after deleting a record.

I also use pseudo-code above, partially because I use my own database
abstraction object and can't remember off-hand what the proper mysql
functions are (and am too busy to look them up at the minute) and
partially because most people also use some form of database abstraction
object (PDO etc) so you'd need to rewrite it to fit in with that anyway.

I also have classes generated for tables that have events for beforeDelete
and afterDelete so I'd use an afterDelete event to compact the ID numbers.

But, as I said at the start, be very aware of Erwin's comments they are
likely true so be sure you want to do this before doing it.



Andy Jeffries MBCS CITP ZCE   | gPHPEdit Lead Developer
http://www.gphpedit.org | PHP editor for Gnome 2
http://www.andyjeffries.co.uk | Personal site and photos

Re: Updating item numbers (or IDs)

On Thu, 27 Apr 2006 08:53:33 GMT, Andy Jeffries

Quoted text here. Click to load it

Thanks guys - appreciate the informative replies.

Re: Updating item numbers (or IDs)

Try to avoid situations where you need and autonumber id field to be sequencially perfect.  However
you might be dealing with an odd situation.

I found that the best thing to do. Is to do a table backup. You don't have to back up the entire db
just the table in question.

first do a full backup of your table.
then do a data export of the same table. WHen exporting data I like to use the ~ as the separator
because the , may be contained in some data fields and scews up the field separation.
next open up that exported data in excel. Go to tools > import text file  make sure to tell excell
the separator is ~
 you will see all your data - hopefully the primary id autoincrement is the very first column. Do a
sort on that column adn then delete that column, save that file
now you delete or drop table from your db.  
Then open up the saved exported table in a word processor and copy the sql that build the table
structure, copy and paste that code into the SQL section of phpadmin and go, this will recreate the
structure of the table.
Next  go to the bottom of that page and you will see, insert text file, select that and you are
taken to a page/form to upload data. Make to set ~ as the separator and lower on the page select the
checkbox that says "local" or the upload sometimes won't work.
Then your data is reinserted and autonumberd starting from 0.

Note: if you have a huge amount of data in the data text file, you can only upload 2 megs at a time.
In situations like that, use you editor to split the file in half and upload each section.
This process is easy to do, sounds much worse that it really is, However there is potential for
disaster.  Try practising on a home server first. get it down. If all else fails at least you have a
full and complete back up of the data and you can dump that table back and get back to where you

Quoted text here. Click to load it

Re: Updating item numbers (or IDs)

Gleep wrote:
Quoted text here. Click to load it

This procedure doesn't work if you have foreign keys.  You need to change those,  

And yes, having holes in the sequence should not be a problem.

BTW - this should be in comp.databases.mysql - it's not a PHP question.

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Site Timeline