Re: cross-table constraints?

Kevin Hunter Kesling wrote
>      CREATE TABLE cost (
>        id          integer NOT NULL PRIMARY KEY,
>        period_id   integer NOT NULL REFERENCES vintage (id),
>        process_id  integer NOT NULL REFERENCES process (id),
>        value       real    NOT NULL,
>
>        UNIQUE (period_id, process_id)
>      );

A check constraint can only reference the same table as on which it is
defined so you will most likely, in some place, define either a trigger or
wrap the relevant constraint checking into an API function and only modify
the relevant database items via that function.

That said you can create a relation containing all the valid combinations
and then use a foreign key constraint on the cost side so that only defined
combinations can be costed out.

Two other comments to consider.  The "cost" relation defined above, if you
de-normalize it via the foreign keys, ends up having two "analysis_id"
fields - the one on vintage and the one on process.  It is possible that
these two field may not be in sync - in addition to the "minimum period"
error you already have identified.  The reason for this problem is that you
are using artificial keys for your relationships instead of natural keys.

I may espouse on this more later but cannot at the moment.  While surrogate
keys are not evil they are also not something to be used lightly and in as
complex a model as this using natural keys does have its advantages.  Since
you can define multi-column foreign keys the same analysis_id on the cost
table can be related to other tables in two separately defined "references".

Something to ponder and it may help solve both problems.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/cross-table-constraints-tp5768724p5768733.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.