Re: Can't use subselect in check constraint

> I assumed check constraints are checked on insert/update, so they would
not need to be re-checked when the tables referenced in the check constraint
were updated If people could live with that, then it seems that it would be
possible.

Well, if you work with the assumption that a constraint is used to guarantee
some sort of consistency, then checking on insert/update wouldn't work. If
you modify the values in the underlying check constraint, you could end up
with an inconsistent state in the database.

Take for example a foreign key... when you insert a record into table B
which has a foreign key into table A, the constraint is checked to insure
that the value in B is also in A. Now you delete that value from A.
According to how the tables were designed, the foreign key value should
either prevent the deletion in A, NULL the foreign key value in B, or set
the foreign key value to a default value. Otherwise, the deletion from A
would create an inconsistent database (an orphaned foreign key).

Foreign keys solve this by creating not only triggers on the table with the
foreign key, but the table referenced by the foreign key. The problem with
doing this for subqueries in a check constraint is that you have to figure
out which tables need these new triggers. What's makes it nigh impossible is
that you can use a function in a subquery, and that function can reference
still more tables. I would say the only good way to implement this would be
for the one who designed the database to create the appropriate triggers to
maintain a consistent state.

> However, it seems like this is the system telling me what I am trying to
do is a bad idea. I have put a bunch of validation codes in one table with
descriptive names so I don't have to have a whole bunch of little tables for
validation codes.

A lookup table then... not a bad idea.

> Different types of codes may have the same value, so I can't have a unique
index on the code, although I do have one on the type+code combination. I
thought I could use a check with a subselect so codes could be added in the
future without dropping and reloading the table.

Is the type in the validation codes lookup table, or in the table that you
want the check constraint on? If so, you can just do a foreign key that
references both columns (codetype and codecol).

> Come to think of it, I wonder if I couldn't just to a foreign key
relationship like:
> constraint thistable_fkey foreign key ('codetype', codecol) references
mycodetable (codetype, codecol)
> Can you use a constant in a foreign key?

Hmmm, I don't know if you can use a constant in a foreign key, but there's
nothing preventing you from creating a table with constant values (you can
even add some triggers to keep them constant). But ideally, your validation
codes table would have type and code columns that can be used for a foreign
key.

Greg