Hi,

> I have a table MY_TABLE with a integer field NUM.
> NUM must be unique, so i create a index.
> When i insert a record in the table, i must check if NUM exists.
> If NUM exists, i calculate Max(NUM)+1, and this will be the new

value.

> (all this is very tipic)

For a unique constraint, you should create an unique
constraint, not an index.

If this number has a purpose of an unique identifier
only, why don't you use a "generator" instead of your
pseudo auto-inc algorith?



--
With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL &
MS SQL Server.
Upscene Productions
http://www.upscene.com

> What is the best/quick way to do this?
>
> At the moment, have this code in a Before Insert Trigger
> ...
> DECLARE VARIABLE N INTEGER;
> BEGIN
> SELECT NUM
> FROM MY_TABLE
> WHERE ( NUM = NEW.NUM )
> INTO :N;
>
> IF ( N IS NOT NULL ) THEN
> BEGIN
> SELECT MAX(NUM)
> FROM MY_TABLE
> INTO N;
> NEW.NUM = N+1;
> END
> END
>
>
> Is it better perhaps to make this checking in the client (Delphi)
> program? Why?