Jeff
You can make a clean start but it takes work.
1) create new table clients_tmp - same fields - no indexes.
add a field and make it a simple integer - say idx_int
2) insert all the fields into client_tmp from clients
place the client.idx field into client_tmp.idx_int
a) At this point, you will have a idx starting at 1 and
ending at however many rows you have.
b) the idx_int is your cross reference between old and new.
3) replace the old client id with the new one from 2-b.
4) rename the clients table and drop the idx_int field.
I'm assuming a mixture of sql code and application code.
The above is a lot of work simply to avoid consecutive index
numbers. There is probably another way - isn't there always?
No doubt...
David
PS By destroying the client idx field, you loose information.
That is, how many total clients have been to your site. Total
current clients in the number of rows in client table. Seems
like management would like to know that. If you have a
timestamp field, you could track it over time. Hum... I've
crossed over to the suit side. Sorry.
-----Original Message-----
From: Jeff Habermann [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 28, 2002 4:45 PM
To: [EMAIL PROTECTED]
Subject: auto_increment question
This is my first time posting to this list, so please forgive me if I am not
doing this correctly. I will break this into post into two parts: what i
want to know and why. Is there a way to get auto_increment fields to fill
in gaps in the numbering sequence if some records have been deleted?
Here is an example of my problem:
I have two tables in MySQL:
1.web_hits: date datetime not null, client mediumint unsigned not null, URL
varchar(500) not null
2.clients: idx mediumint unsigned not null unique auto_increment, name
varchar(255) not null
"web_hits" holds information about web traffic on our network. There are
more fields that what i have listed, but this is all that is needed for this
example. It records the time of the web hit, the URL, and the ID of person
who made the hit. The ID can be looked up in the "clients" table.
Now, after a while the web_hits table starts to get really big, because of
this we start deleting the old entries from the web_hits table based on the
date field. Because of this deletion some clients in the clients table may
not be used any more. So we have a query that will delete those from the
clients table as
well. Now, because of this deletion, there are sequence gaps in the "idx"
field. We would like to be able to use those numbers again for incoming
clients...Is this possible?
Thank you,
Jeff
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
