Subject Query help: getting non used records in a table
Author Diego Rodriguez
Hi all,

I have a table LOTTERY with an integer field NUMBER that is also primary
key. My app will fill this table with random numbers between 0 and 99999
everytime that is needed. When an event happens I generate a random number
and try to insert that number. Because it is for a lottery, there must not
be repeated numbers, and this is ensured by primary key. If the random
number is repeated, an exception "violation of primary key" is raised, and I
try with another random number. I know this model will have problems when
the table is almost full, because I will get a lot of exceptions each time
an insert is made, so if the exception is raised twice, I search for a non
used number in the table (lottery not really random, but will work). The way
I get this numbers is getting all numbers ordered and programatically find
any gap between numbers...

It is posibble to get a non used number directly with a query?

I'm using Firebird 1.0.3 in a Debian Linux platform.

Many thanks