Question about autoincrement ID
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/
signature.asc
Description: Digital signature
Re: Question about autoincrement ID
[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
Re: Question about autoincrement ID
On Thu, Mar 23, 2006 at 11:04:55AM -0500, [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (saf) wrote on 03/23/2006 10:50:10 AM: 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. So I must do a big SELECT and then check my self every time (for each INSERT), which IDs are free? Hmm if the table has more than 100 000 entries, this will slow down my system. Specialitty because the check function would be written in PHP. -- Best regards, saf http://www.trashmail.net/ signature.asc Description: Digital signature
Re: Question about autoincrement ID
[EMAIL PROTECTED] (saf) wrote on 23/03/2006 16:10:04: On Thu, Mar 23, 2006 at 11:04:55AM -0500, [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (saf) wrote on 03/23/2006 10:50:10 AM: 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. So I must do a big SELECT and then check my self every time (for each INSERT), which IDs are free? Hmm if the table has more than 100 000 entries, this will slow down my system. Specialitty because the check function would be written in PHP. Lots of ways round this. Instead of deleting records, add a boolean deleted flag. All selects then need to add and deleted = 0. But you can find a (random) deleted row with select id from table where deleted = 1 limit 1. If this returns a result, use update to re-populate that record, clearing the deleted flag. If it returns nothing, use insert to create a new record. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about autoincrement ID
On Thu, Mar 23, 2006 at 04:17:44PM +, [EMAIL PROTECTED] wrote: Lots of ways round this. Instead of deleting records, add a boolean deleted flag. All selects then need to add and deleted = 0. But you can find a (random) deleted row with select id from table where deleted = 1 limit 1. If this returns a result, use update to re-populate that record, clearing the deleted flag. If it returns nothing, use insert to create a new record. Great idea!! Many thanks!!! -- Best regards, saf http://Trashmail.net/ signature.asc Description: Digital signature
Re: Question about autoincrement ID
So I must do a big SELECT and then check my self every time (for each INSERT), which IDs are free? No, you just ignore deleted IDs. What's the point? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about autoincrement ID
[EMAIL PROTECTED] (saf) wrote on 03/23/2006 11:10:04 AM: On Thu, Mar 23, 2006 at 11:04:55AM -0500, [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (saf) wrote on 03/23/2006 10:50:10 AM: 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. So I must do a big SELECT and then check my self every time (for each INSERT), which IDs are free? Hmm if the table has more than 100 000 entries, this will slow down my system. Specialitty because the check function would be written in PHP. -- Best regards, saf http://www.trashmail.net/ No, you should quit trying to tell the database how it should implement auto_increment. If you don't want a monotonically increasing integer value to be automatically generated for each new record (or attempted new record) then simply don't use auto_increment. At that point you can make your ID values anything you want because you are going to be completely in charge of creating them. There are dozens of great reasons why the database has an auto_increment function built into it. There are probably as many reasons why doing what you propose to do is normally considered very bad practice. What's the real reason you don't want to let auto_increment do its automatic numbering? Many of us on the list manage databases with millions or billions of rows in our tables and we DO NOT even attempt to fill in the gaps as you propose to do. There is just no good reason to do it, and several good reasons to NOT do it. One important thing to remember: You should not let UI design requirements dictate your DB design. Most developers who design the database just to support the front end up regretting the decision. Those designs are either impossible to extend or impossible to manage or both. You should always design for an efficient database and adjust your retrieval methods to present the data in the manner requested, not the other way around. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Question about autoincrement ID
On Thu, 23 Mar 2006 [EMAIL PROTECTED] wrote: To: saf [EMAIL PROTECTED] From: [EMAIL PROTECTED] Subject: Re: Question about autoincrement ID One important thing to remember: You should not let UI design requirements dictate your DB design. Most developers who design the database just to support the front end up regretting the decision. Those designs are either impossible to extend or impossible to manage or both. You should always design for an efficient database and adjust your retrieval methods to present the data in the manner requested, not the other way around. Shawn Green Database Administrator Unimin Corporation - Spruce Pine IMHO I think the database is the central core of a DB driven website. Therefore it should be the first thing designed in a DB driven website. Everything else in a DB driven site should then be built around the expected functionality of the database. So, if one starts out by designing a database (and it's server(s)) with optimum performance and upgradability as design goals, you won't go to far wrong. Just my 2c. Keith Roberts In theory, theory and practice are the same; in practice they are not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
