NIPP, SCOTT V (SBCSI) wrote:
I am working on a database application for Unix user accounts. I want to be able to have a system that will provide me the next available numeric user ID. I have created a 2 column index table that simply includes UID and user name. This field is autoincrement and the primary key for the table. Currently this field only contains a very small sample of users. Basically, I have numeric IDs 3000, 3001, 3008, and 3028. My problem is that I am not sure how to query this table to get the next available numeric ID which is 3002. If I do an insert into this table without specifying a value for the auto_increment column it comes up with 3029. Is there a way to query and/or insert such that it comes up with the next unused numeric value?
Why on Earth would you want to do that? Do you have a genuine reason?
The whole point of auto_increment columns is that you do not need to reuse the ID numbers. Using an UNSIGNED INT column, there can be 4294967295 values. Do you really expect to go over that? If so, an UNSIGNED BIGINT can have 18446744073709551615 values. If you think you're going to go over that, you're f'n crazy.
Do not worry about holes in the sequence. If you application depends upon sequential numbers, then you've written it wrong and it's using the auto_increment column for the wrong purpose.
There are good reasons, too. If you delete user 3000 and the next user gets that UserID, now anything left over from the old user 3000 is now related/linked to the new user 3000. How do you even tell there was an old user 3000? Wouldn't you want to be able to detect that? I'm sure there are other reasons, too...
--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals – www.phparch.com
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
