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.