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
