How To Reuse Deleted Unique Numbers

[afoote]

Hi.. I'm hoping this is possible and someone has an answer for me.

I have a MySQL table.. a membership table with a field for name, email and phone and an UNIQUE auto_increment field for membership number... now my members are only temporary, after a month or so they are deleted. I only have about 50 people in this table at a time but the problem is my membership numbers are up to 1000 now.. but I only have 50 members.. if I delete say member #500 , when a new members joins it give him #501, even thought 500 is no longer taken. Think you get what I mean.

Is there a way I can get mysql to re-use the old numbers that have been deleted? But still making each number UNIQUE.. as I can't have 2 members with the same number at the same time.

Thanks A Lot
AJ Foote

 

[swampBoogie]

No, I don't see why this is a problem. The only purpose of auto_increment is to provide unique values,not sequential numbers.

 

[sleipnir214]

Having unique indeces is much more important than making sure all possible index values are used. I wouldn't worry about it.

If you're concerned about running out of index numbers, first make sure your auto_increment column is an unsigned type.

If you're still worried about running out of numbers, change the type of your auto_increment column to "bigint unsigned" -- you'll have to eat up numbers to 18446744073709551615 before you run out.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[afoote]

Thank for replies guys. I understand it's not really a problem, I don't care about it myself.

The reason I asked was because I have my members login by their member number and since I only have no more than 100 members. I just thought that it would be easier for them to have a number between 1 and 100 rather than 1039 (that's what it's up to now). I could always change my login and have them choose a username and password rather than their "number" and password.

I'm a new members to this site. I must say I'm impress I got a reply so fast... thanks for that. I will use this site again and try to help myself whenever I can. I don't have much experience with MySql (or any DB) but can help in other areas.

AJ Foote
ajfoote@rogers.com

 

[afoote]

Thanks for replies guys. I understand it's not really a problem, I don't care about it myself.

The reason I asked was because I have my members login by their member number and since I only have no more than 100 members. I just thought that it would be easier for them to have a number between 1 and 100 rather than 1039 (that's what it's up to now). I could always change my login and have them choose a username and password rather than their "number" and password.

I'm a new members to this site. I must say I'm impress I got a reply so fast... thanks for that. I will use this site again and try to help myself whenever I can. I don't have much experience with MySql (or any DB) but can help in other areas.

AJ Foote
ajfoote@rogers.com

 

[swampBoogie]

I would recommend using a choosen username instead of an arbitrary number.