Kevin Hunter wrote: > At 3:26p -0400 on 13 Jun 2007, Stefan Kaltenbrunner wrote: >>> The way that I currently know how to do this in Postgres is with >>> PLpgSQL functions. Then I add something like >>> >>> CONSTRAINT away_team_is_playing CHECK ( NOT teamIsPlaying( >>> awayteamid, timeid ) ) >>> >>> to the table schema. >> >> well doing it that way is usually not a good idea at all (you cannot >> actually use arbitrary queries in a CHECK constraint in pg either - >> using a function to hide that is cheating the database - oracle might >> actually be more(!) clever here not less ...). this why you can get >> into all kind of weird situations with losing the integrity of your >> data or running into serious issues during dump/restore for example. > > I was /hoping/ for a response like this! Thanks! Okay. I'll bite. > Why can't they be used in general? Is it the same problem that the > trigger has (below)? ok consider the following scenario: you have table A with a check constraint saying the a given column depends on the result of some arbitrary queries on table B (maybe something as simple as "this flag can only be set to true if there are at least 5 items of this kind in table B). So after a while you added some data to both tables and A has set that column to true for a few rows. Now you manipulate B in a way that woudl cause maybe one constraint to fail in A IF and only IF it get's reevaluated. One day later your server decides to commit suicide - so you get the spare one dig out the backup from the day before and try to restore that dump. First you will get the schema restored from the backup - next all the data will be put in into the tables and then ? "Booooom". The third major step in restoring the database is readding all constraints - if the CHECK constraint get's readded it will get evaluated by the database fully (ie row-by-row) - but because your data is inconsistent you will get a constraint violation on the now inconsistent data ... The trigger would avoid that restore problem because it would not be invoked in a similiar fashion (ie they "trigger" on UPDATE/DELETE/INSERT which are already done at that point of the restore). That is just one of the most obvious failures - trying to implement that kind of "arbitrary complex query based foreign-key like" constraint is always very difficult. > >> What you need to do here is to use a trigger. > > From online docs regarding Oracle, this is not 100% safe either: > > (http://download-east.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_co.htm) > > 'To enforce this rule without integrity constraints, you can use a > trigger to query the department table and test that each new employee's > department is valid. But this method is less reliable than the integrity > constraint. SELECT in Oracle Database uses "consistent read", so the > query might miss uncommitted changes from other transactions.' > > It seems to me that there are certain situations where, especially in a > highly normalized data model, that you'd /have/ to have multiple checks > of even other tables. What theory am I missing if this is not the case? well - the main point why the trigger is better is because it will cause you less operational issues, what it cannot guarantee you either is that you cannot manipulate table B in a way that would violate the checking logic of your constraint in table A (well you could use a trigger on A to validate that in the other direction - but that opens up another big can of worms). The best way is to design your schema in a way that you can use real foreign key constraints to enforce various things or CHECK constraints that only deal with data in the very same row(ie neither do queries on other tables nor depend on OTHER rows than the one that is manipulated in the same table). Stefan
