At 1:45 PM +0100 7/4/01, matthew yee-king wrote:
>sorry if this is old hat...
>i am trying to figure out a way to assign primary keys to my database tables.
>I have previously used ints with auto_increment but i am wondering
>what happens when you reach the highest number possible for an
>int... is there some way of 'reclaiming' primary keys for the system
>when an entry in a table is removed?
How many records are you expecting? Using an unsigned integer as a
key, you get over 4 billion entries. So, if you were entering 10
records/second, around the clock, you'd only run out of keys after
more than 13.5 years. I think you'd run out of disk space first!
If you really wanted, you could double the size from 32 to 64 bits
and go with a BIGINT (or unsigned bigint). I don't think you'd have
ANY problem then!
>Maybe some sort of system that provides ints from a pool, reclaiming
>them when entries in tables are removed?
That would slow things down, but you could implement that yourself:
Create table key_list (
Key_value integer unsigned not null primary key,
Key_used tinyint default 0
Set Key_used = 1 for used keys, reset to 0 when keys are deleted,
then do a select min(key_value) where Key_used=0 to get the lowest
+------ Factoid: Of the 100 largest economies in the world, 51 are ------+
| Steve Edberg University of California, Davis |
| sbedberg@stripped Computer Consultant |
| http://aesric.ucdavis.edu/ http://pgfsun.ucdavis.edu/ |
+--- corporations ------ http://www.ips-dc.org/reports/top200text.htm ---+