At 10:29 PM 20-01-02 +0100, you wrote:

>I have a table with the following fields:
>
> N_MOV INTEGER NOT NULL
>, D_MOV DATE NOT NULL
>, ...
>
>I want to add a PK that will verify that no two records are inserted with
>the same N_MOV in the same YEAR.
>
>How can I do that ?

With difficulty. You will need to store the year as a distinct integer column and apply a UNIQUE constraint across this column and N_MOV. I strongly recommend an atomic primary key (generated) and triggers to enforce the uniqueness of the N_MOV | MOV_YEAR combination.

How you get MOV_YEAR depends a lot on the version of IB you are using. EXTRACT(YEAR from ...) is available in IB6/FB dialect 3. For others you would need to involve a UDF or actually create the data on the client (not nice for keys).

It is a Very Bad Idea to involve dates in keys, too. Key columns should be atomic, small and of simple data types. Needless to say, you can expect complications if you define key columns that depend on triggers and/or expressions.

Have you considered buying a good book about SQL? You'll find some recommendations at http://www.interbase2000.org/bookstore.htm

regards,
Helen

All for Open and Open for All

Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________