--- In firebird-support@yahoogroups.com, "women_lover_best"
<talbronstien@g...> wrote:

> Please see below answer...
>
> AFAIK you can have as many UINQUEs on a table as you want. But anyway:
> You
> can make sure by using a before InsertUpdate-trigger checking for an
> exists(select FIELD from table where FIELD=VALUE) on your FIELD and
> VALUE.

Um, not really, or rather not always.

Your trigger is NOT transaction safe. That means that if another
transaction has inserted a record the same as you, but that other
transaction is still active, your exists check will not see it. The
only time this logic would work is if your database had only a single
user, and that single user only ran a single transaction at a time.

If you already have a unique constraint, then the trigger will also
slow down inserts and updates WITH NO BENEFIT.

> ..............................
>
> ....My question..
> How to define UNIQUE on a column>?

Declare it as a constraint on the table. The constraint will the
police the uniqueness even with data not visible to your transaction.

> Also..what is VALUE above, a variable declared in trigger..?

It would be NEW.FieldName, but don't do it this way, that is not what
triggers are for, but rather what unique constraints are for.

Adam