By replacing deleted records with new information, you run the risk of
misidentifying data in related tables. What if you had a record in a table
called "person" with an ID of 6 that belonged to Mary Jones. You delete it
and create a new record 6 for Bob Mondo? Let's say you have a related
table that contains phone numbers AND that you somehow neglected to erase
the records from phnumbers where the person_id was 6 when you deleted
Mary's record from the person table. Now that you have created Bob's
record, those phone numbers seem to belong to him. Based on your data how
could you tell they weren't Bob's phone numbers? Can you see where this
can become a complicted situation if your database were more complex?
Gaps in your sequence numbers are a normal consequence of having an active
database. You can expect a sequentially assigned number to relate to only
1 record per table. When that record goes away, it's sequence number goes
away too.
IF you MUST recycle your sequence numbers for some reason, add a field to
your table so that you can flag a record as "deleted". Then when it comes
time to insert new records you will have to UPDATE the flagged records
with the new information and reset the flag to "active". For any data
beyond that which will fit into the available "deleted" records, you will
have to revert to a standard INSERT statement. IMHO, that is way too much
effort to spend on keeping your sequentially assigned numbers in
sequential order and without gaps. Your database performance will be
severely degraded by all of the extra management you have to perform each
time to need to add a record to your database.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Mike" <[EMAIL PROTECTED]> wrote on 09/19/2004 04:59:02 PM:
> hi.
> I have a rather childish question on tables and auto increment fields.
> Scenario: I have a table with an <int auto increment primary key> field.
The
> deal is that everything works fine (I'm talking about the auto
> incrementation part)
> until I choose to delete a row. This creates a gap in the primary key
field.
> And my question is: I'd like to know if there is an easier way to keep
track
> of these gaps, instead of specifically iterating through the table and
> stopping where you find one. To accomplish this, I use this function:
>
> function GetUntakenNrCrt($tabel) {
> $nrCrt = 1;
> while(1>0) {
> if(!GetSingle("select nrcrt from $tabel where nrcrt='$nrCrt'")) return
> $nrCrt;
> $nrCrt++;
> }
> }
>
> function GetSingle($query) {
> $q = mysql_query($query);
> if(mysql_num_rows($q) > 0)
> while($p = mysql_fetch_row($q)) return $p[0];
> else return false;
> }
>
> The reason is that I want a table with continous records in the primary
key
> field (1, 2, 3... instead of 1,6,23...).
> Can anyone suggest a different (and easier) method? Thanx a lot!
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
