Re: pointer to feature comparisons, please

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