At 11:50 AM 21-01-02 +0100, you wrote:

>>I strongly recommend an atomic primary key (generated) and triggers to
>enforce the uniqueness of the N_MOV | MOV_YEAR combination.
>
>could you please expand on this ?

Generated atomic primary key? Establish a NUMERIC(18,0) primary key and populate it by trigger (or otherwise) from a generator. Place a UNIQUE constraint (or a UNIQUE index, if you need it) on the composite column structure that has been surrogated. Any foreign keys will refer to the primary key, not the columns in the index. In InterBase, it's wise to create foreign keys *before* you start adding unique indexes elsewhere in the table.

Or triggers to enforce the uniqueness of the N_MOV | MOV_YEAR combination? If every incoming date has to be tested to ascertain whether it is unique for year, you would need a trigger to extract YEAR from the date in order to test it.

>From what you said, I find much easier to add a MOV_YEAR field to the table
>and fill it from my D3 program.

If your solution is calculate Year from DecodeDate() in Delphi and pass it directly, all that expression stuff is avoided. The unique constraint on the movement/year combination will catch an exception without having to worry about the actual date.

>>It is a Very Bad Idea to involve dates in keys, too.
>
>not my idea.
>
>The world out there requires that.

No, the world out there doesn't require you to involve dates in keys. The world out there requires movement numbers to be unique within year.

>Every warehouse movement must be identified by a progressive number that is
>unique in the year it takes place.

So: but there is no law that says it has to be the primary key of a table. But there are rules of healthy practice in database design which include making keys atomic (independent or meaning as data).

--hb


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