Subject Re: Best way to check a value
Author Paco Ruiz
Hi Paul

> > I have a table MY_TABLE with a integer field NUM.
> > NUM must be unique, so i create a index.
> An index doesn't guarantee uniqueness. A UNIQUE constraint does. As
> does a primary key.

Sorry, i forget to say UNIQUE index.

> Is NUM supposed to be some kind of record ID? Then I suggest you make
> it the PK and feed it from a generator:
> create table MY_TABLE
> (
> NUM integer not null primary key,
> ...
> ...
> );
> create generator My_Generator;
> set generator My_Generator to 0;
> and in your before-insert trigger:
> if ( New.NUM is null )
> then New.NUM = gen_id( My_Generator, 1 );
> Even if you don't make NUM your PK or a UNIQUE field, you can still
> feed it from the generator.

NUM is a numeration doc/form.
The problem with a generator is that if the insert fails, i will lost
the generator value. I donĀ“t want to loose this value.