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 |
| [EMAIL PROTECTED] Computer Consultant |
| http://aesric.ucdavis.edu/ http://pgfsun.ucdavis.edu/ |
+--- corporations ------ http://www.ips-dc.org/reports/top200text.htm ---+
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
