MySQL primary key reusal

[Al3x]

For my primary key I have it NOT NULL and AUTO_INCREMENT...let's say it's INT(3) and call it "pkey".

 

This is fine and all...but say I have 20 rows with primary keys 1-20...and I delete #15.  When I add another, it gets the key 21.

 

What happens when I have done this like 300 times?...I may still have 20 rows of data, but eventually my pkey will auto_increment out of values.

 

Is the only way to avoid this by setting the pkey to something like INT(5) or more?  Is there a way to reuse unused primary keys automatically?

[bpops]

I BELIEVE (someone else should chime in) that when you do INT(3) that only means the first three digits are RETURNED, but all the digits are there. In other words, INT(1) or INT(5) hold the same amount of information.

[roopurt18]

I can't comment on bpops reply, but why would you want to reuse primary keys?  INTEGER is pretty big.  Chances are you'll trash the entire site and rewrite it before you run out of keys.

[Al3x]

You have a good point roopurt18.

 

Also BPops you are correct.  I just read this in a mysql book I bought:

 

"Integer with a range from -2147483648 to +2147483647.  The number that can be displayed is limited by length.  For example, if length is 4, only numbers from -999 to 9999 can be displayed, even though higher numbers are stored.

 

What does it mean "displayed" though?  Does that mean if I query it in PHP and output it...will it output the entire int?  Or is that considered a display?  I guess I can play around with that one.