easy question

Hello to all,
I have an easy question: how can I find the first not used number in a
field to use it as a primary key?

example:

id name
1 harry
2 mary
4 frankie

for the new row I have to found 3.
Thank you,

tano

<gaetanoortisi@yahoo.it> wrote in message
news:1141247390.491959.94210@i40g2000cwc.googlegro ups.com...
> I have an easy question: how can I find the first not used number in a
> field to use it as a primary key?

This is a frequent misconception, that it is important to fill the "gaps" of
a primary key column. This is almost never worth the effort, so there is
usually not a simple way to do it.

Primary keys need to be unique, but they do not need to be contiguous.

You are not likely to run out of values in an unsigned 32-bit integer. Only
if you insert 1 record every second for 136 years, will you be close to
running out.

You might be reusing primary key values that have been deleted, or which was
allocated but never committed. This is risky, because other tables in your
database might still have those values and by reusing a value that has been
deleted, you would create an implied relationship between records that
really have no relationship.

A better plan is to always assign primary key values by incrementing past
the largest value in the table. Don't try to fill the "gaps".

Even better is to use a mechanism for a monotonically-increasing counter in
the RDBMS implementation. Many RDBMS implementations have mechanisms to
automatically allocate primary key values, to reduce the risk of reusing a
value.

Anyway, in spite of all those warnings, you might have an exceptional case
where it is important to have contiguous values. Here's one type of
solution (for primary key values 0-99999):

create table d (d integer not null);
insert into d values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

select min(d10000.d * 10000 + d1000.d * 1000 + d100.d * 100 + d10.d * 10 +
d1.d)
from (d as d10000, d as d1000, d as d100, d as d10, d as d1)
left outer join mytable as t on t.id = (d10000.d * 10000 + d1000.d * 1000
+ d100.d * 100 + d10.d * 10 + d1.d)
where t.id is null;

Regards,
Bill K.

> > I have an easy question: how can I find the first not used number in a
> > field to use it as a primary key?
>
> This is a frequent misconception, that it is important to fill the "gaps" of
> a primary key column. This is almost never worth the effort, so there is
> usually not a simple way to do it.
>
> Primary keys need to be unique, but they do not need to be contiguous.
>

I know, and I don't want them to be contigues, I want them to be
reusable.
What I have to do is to attach (physically) to every product that is
stored
in the database a paper label that I would like to made recyclable.

>
> Even better is to use a mechanism for a monotonically-increasing counter in
> the RDBMS implementation.

I know auto-increment feature, but it's not what I need.

> Regards,
> Bill K.

Thank you a lot,
tano

thanks