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 u=
se case are the following
> tables.
[...]
=20
> 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.=
*')
> );
>=20
>=20
> The CHECK constraint should achieve that "persons.place_of_birth_id" is a=
lways a country, or a
> (first_order) adminitrative division, or a city (which is defined by "fea=
tures.gtype_id").
>=20
> Though this could be done by creating a multi-column foreign key on
> ("features.feature_id","features.gtype_id"), this would violate the princ=
iples 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 litt=
le 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
