Re: CHECK() Constraint on Column Using Lookup Table

On May 1, 2007, at 12:41 , Rich Shepard wrote:

>   I've seen the syntax for using a lookup table in a CHECK()
> constraint, but
> I cannot find a reference to it. It's not in Section 5.3.1 of the
> 8.2 docs.

I'm not sure I follow. Generally if one has a column the value of
which belongs to a limited set, one uses a lookup table (via a
foreign key) *or* a check constraint, but not both, as it'd be
redundant.

For example, using a lookup table:

CREATE TABLE states
(
    state_code TEXT PRIMARY KEY
);

CREATE TABLE addresses
(
    address TEXT NOT NULL
    , state_code TEXT NOT NULL
        REFERENCES states (state_code)
    , PRIMARY KEY (address, state_code)
);

or, using a CHECK constraint:

CREATE TABLE addresses
(
    address TEXT NOT NULL
    , state_code TEXT NOT NULL
        CHECK (value in ('state_1', 'state_2', ...))
);

In this case, I would definitely use a lookup table rather than a
CHECK constraint as it's much easier to maintain.

I feel I probably didn't answer your question, but this is what I
understood from your description. I guess you might be referring to
using a subquery or lookup function in the check constraint to make
sure the values of state_code are valid values (in the states table),
but that's what a foreign key is doing anyway, and much more
efficiently. Further, subqueries in check constraints aren't
supported in PostgreSQL. You can fake it by wrapping the subquery in
a function, but again, you're just manually doing what foreign keys
are designed to do for you automatically.

Hope this helps.

Michael Glaesemann
grzm seespotcode net