[EMAIL PROTECTED] (saf) wrote on 03/23/2006 10:50:10 AM:
> Hi,
>
> I have a question about autoincremend id:
> If I have an autoincrement id set on my first column field of my
> table and I have the
> following entries:
> 1
> 3
>
> And then I make a INSERT INTO foobar VALUES(''); , the next field
> would be automatically 4:
> 1
> 3
> 4
>
> Is there a possibility to take a free ID to not use too high IDs for
nothing?
> I would like to take the ID 2 and not 4, because ID 2 is free.
>
> My problem is that my system which uses the ID numbers in
> applications which uses them
> as signed int or unsigned int,
> so I will soon have a problem, because I insert (and delete some
> times) many entries in my SQL database,
> but not more than the highest value of an signed integer.
>
>
> --
> Best regards,
> saf
> http://TrashMail.net/
The short answer is "no". The Record #2 already existed. It's current
status is "deleted". If you had other tables that linked their data to
record #2 and you created a new #2 to replace the one you already deleted
then you could possibly be making a "bad" match between the old data and
the new data.
For the sake of data consistency and for all of the other good reasons to
have a relational database, once an auto_increment value has been issued
it's considered used and no other record should ever have that number.
Only if you completely reset your table (see the command "TRUNCATE TABLE")
could it be possibly safe to begin re-issuing the smaller numbers. Again,
it's only possible if all of the child records that used to point to the
old data were also deleted.
Do not rely on the auto_increment value for record sequencing. If you need
your records serialized in some sequential way, you will need to code the
support for those sequential numbers in your application.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
