Re: Immutable functions, Exceptions and the Query Optimizer

Cochise Ruhulessin wrote:
> Regarding your question about what the CHECK constraint should achieve, I had abstracted by use case
> into Books/Book Types, which may have caused some vagueness. The actual use case are the following
> tables.

[...]

> CREATE TABLE persons(
> person_id int8 NOT NULL PRIMARY KEY,
> place_of_birth_id int8
> REFERENCES features (feature_id)
> ON UPDATE CASCADE
> ON DELETE RESTRICT
> INITIALLY IMMEDIATE,
> CHECK (features_get_feature_code(place_of_birth_id) ~ 'PC.*|ADM.*|PP.*')
> );
>
>
> The CHECK constraint should achieve that "persons.place_of_birth_id" is always a country, or a
> (first_order) adminitrative division, or a city (which is defined by "features.gtype_id").
>
> Though this could be done by creating a multi-column foreign key on
> ("features.feature_id","features.gtype_id"), this would violate the principles of normalization.

True; but if you don't mind that, it would be a nice solution
since you already have a unique index on features(feature_id, feature_code).

> Of course this could also be achieved by a TRIGGER, but that seems a little redundant to me.

I think a trigger is the best solution here.
Why is it more redundant than a CHECK constraint?
Both will do about the same thing, with the advantage
that the trigger solution would be correct and won't
give you any trouble at dump/reload time.

Yours,
Laurenz Albe