List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:July 4 2001 6:13pm
Subject:Re: choosing primary keys
View as plain text  
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 
available key.

+------ Factoid: Of the 100 largest economies in the world, 51 are ------+
| Steve Edberg                           University of California, Davis |
| sbedberg@stripped                               Computer Consultant |
|         |
+--- corporations ------ ---+
choosing primary keysmatthew yee-king4 Jul
  • Re: choosing primary keysSteve Edberg4 Jul