First free ID ?? - please help

[Xkarlos77]

Hello, I have problem:

is there any way how get the number of first free ID in a table ??

table look like this :

ID, Name, ...
1,
3,
5,
6,

Is it possible without inserting a record ???

thank you

 

[swampBoogie]

No.

 

[r937]

actually, there is, but it involves a right join with an integer table, and setting up the integer table to have as many entries as the maximum ID is painful

also, i cannot think of any reason why you would want to find the "first free" ID, if the ID is some kind of surrogate primary key -- re-using an ID is a very very bad idea


rudy

 

[Xkarlos77]

Yes may be,

but when I use still new and new ID,
then the number is greater and greater
and can go over the limit of INTEGER number.

??

so deleted IDs is better not re-using ???

thanks

 

[sleipnir214]

I agree with r937. Don't reuse the IDs.

In general, I recommend you use "int unsigned" rather than "int" for IDs. That will give you 4294967295 (2³²-1)records to insert before you run out of IDs.

And if you are still worried about running out of IDs, then use "bigint unsigned". This will allow you to insert 18446744073709551615 (2⁶⁴-1) records before you run out of IDs. Want the best answers? Ask the best questions:

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

TANSTAAFL!