Re: CHECK evaluation error when using more than one table - Mailing list pgsql-bugs

pgsql-bugs@postgresql.org writes:
> CREATE TABLE ninhada (
>     c_id_mae int,
>     n_id int,
>     c_id_pai int,
>     n_dta_nasc date,
>     PRIMARY KEY (n_id, c_id_mae),
>     FOREIGN KEY (c_id_mae) REFERENCES caes (c_id)
>        ON DELETE CASCADE,
>     FOREIGN KEY (c_id_pai) REFERENCES caes (c_id)
>    ,
>      CHECK (c_id_mae in
>         (select c.c_id from caes c  where caes.c_id=c_id_mae AND c.c_sexo=0)
>      )
> );

Sub-selects aren't supported in CHECK constraints (and 7.1 will give
an error message to that effect, rather than bombing out at runtime).

The semantics of such a thing aren't very clear anyway: which rows of
which tables are being constrained?  In the general case we'd have to
re-execute the check expression for every row of its table after any
modification to any row of that table or any other one referenced in
the CHECK clause.  That's not going to be practical.

You could fake it by putting the sub-select in a function, but be aware
that the function is only going to be called at insert or update of
a row of ninhada; there isn't any cross-check on updates to caes
that may invalidate ninhada rows.

            regards, tom lane