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:
>Hello
>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!

See
	http://www.mysql.com/doc/C/o/Column_types.html
and
	http://www.mysql.com/doc/N/u/Numeric_types.html



>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.

-steve
-- 
+------ 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 ---+
Thread
choosing primary keysmatthew yee-king4 Jul
  • Re: choosing primary keysSteve Edberg4 Jul