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)?
> 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?
(I'm curious as well for another project on which I'm working that
does use pg and currently uses a function in just this fashion.)
Thanks,
Kevin
