Subject Re: [firebird-support] Best way to check a value
Author Paul Vinkenoog
Hi Paco,

> 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.

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.

> Is it better perhaps to make this checking in the client (Delphi)
> program?

No, because this is something you want to happen automatically when
new data are entered. This is how you want your data organized, so you
should take care of it within the database itself. This enhances the
robustness (suppose one day someone approaches your database with
another client?)

Paul Vinkenoog