Thread: Can't use subselect in check constraint
Is this something that will change anytime soon? I could not find anything about it in the todo... Thanks! Ian A. Harding Programmer/Analyst II Tacoma-Pierce County Health Department (253) 798-3549 mailto: iharding@tpchd.org
> Is this something that will change anytime soon? I could not find anything about it in the todo... My understanding is that check constraints can't be queries; at least I have never seen them. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
"Ian Harding" <ianh@tpchd.org> writes:
> Is this something that will change anytime soon?
It's not high-priority in my mind. The difficulty is the lack of a
planning step for constraint expressions. Maybe we could fix this
when we redesign querytrees; right now I think there's too much cruft
in the way (memory leaks, state kept in the querytree, etc etc)
regards, tom lane
On Wed, 19 Dec 2001, Tom Lane wrote: > "Ian Harding" <ianh@tpchd.org> writes: > > Is this something that will change anytime soon? > > It's not high-priority in my mind. The difficulty is the lack of a > planning step for constraint expressions. Maybe we could fix this > when we redesign querytrees; right now I think there's too much cruft > in the way (memory leaks, state kept in the querytree, etc etc) Also, AFAICS, check constraints with subselects are constraints on the other tables named in the subselect as well and it could be on a case which the current check constraints don't fire (for example on delete from a table where a set value function is used, like check (select count(*) from foo)>col1) or something like that).
> > Is this something that will change anytime soon? I could not find anything about it in the todo... > > My understanding is that check constraints can't be queries; at least I > have never seen them. Considering that you could potentially have to reevaluate each CHECK constraint every time you updated any table in the database, I would think it would be a *real* bad idea to allow that. There is no way to automatically create the triggers needed to satisfy "When should I check the constraint?" If it can't be constructed as a check against a literal value or values, or a foreign key constraint, it should be up to the user to maintain that consistency check with the appropriate triggers. Greg
> > On Wed, 19 Dec 2001, Tom Lane wrote: > > > "Ian Harding" <ianh@tpchd.org> writes: > > > Is this something that will change anytime soon? > > > > It's not high-priority in my mind. The difficulty is the lack of a > > planning step for constraint expressions. Maybe we could fix this > > when we redesign querytrees; right now I think there's too much cruft > > in the way (memory leaks, state kept in the querytree, etc etc) > > Also, AFAICS, check constraints with subselects are constraints on the > other tables named in the subselect as well and it could be on a > case which the current check constraints don't fire (for example > on delete from a table where a set value function is used, like > check (select count(*) from foo)>col1) or something like that). It this a TODO item. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> 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