Re: Can't use subselect in check constraint - Mailing list pgsql-general
From | Gregory Wood |
---|---|
Subject | Re: Can't use subselect in check constraint |
Date | |
Msg-id | 00c101c18a4f$1da92b80$7889ffcc@comstock.com Whole thread Raw |
In response to | Can't use subselect in check constraint ("Ian Harding" <ianh@tpchd.org>) |
List | pgsql-general |
> 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
pgsql-general by date: