Re: Sequences without blank holes

The best thing is: never let the end users see the primary key. Period.
Primary keys are NOT business objects !
If your users need some IDs for the product, better assign some string
ids, but I bet the app can be written so they never need any IDs.

Just my opinion.

Cheers,
Csaba.

On Thu, 2003-11-06 at 09:01, MaRcElO PeReIrA wrote:
> Hi guys,
>
> I have been using the following table (short, short,
> short version):
>
> CREATE TABLE products (
> prod_id SERIAL,
> description TEXT
> );
>
> BUT, there is lots os users blaming because the holes
> in the [prod_id] field (and, of course it as supposed
> to be like this, because sequences only increase their
> values and never rollback).
>
> So, a real SELECT statement would return:
>
> $ select * from products;
> prod_id | description
> --------+---------------------
> 1 | S470DXBLM
> 12 | S470DXABM
> 33 | RG250DX
> --------+---------------------
> (3 rows)
>
> and it is ok to me, but not to the users.
>
> How can I assure a ''sequence WITHOUT holes''?
>
> Sequences?? Triggers?? Functions??
>
> IF I had ``very few lines'' on the table, ``very few
> users'' AND it was a kidding software, I would use:
>
> $ SELECT max(prod_id)+1 FROM products;
>
> to know the values of the next prod_id, but I really
> think it is not the best way to do that.
>
> Could you help me in this way??
>
> Thanks in advances and
> Best Regards,
>
> Marcelo Pereira
> PHP/SQL/PostgreSQL
> Universidade Estadual de Campinas
> São Paulo / Brazil
>
> Yahoo! Mail - 6MB, anti-spam e antivírus gratuito. Crie sua conta agora:
> http://mail.yahoo.com.br
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend