On Sat, 30 Sep 2000, Philip Warner wrote:
> At 18:09 29/09/00 -0700, Stephan Szabo wrote:
> >
> >How do other systems (Oracle, etc...) handle the subselect and arbitrary
> >function cases?
> >
>
> Dec RDB seems to go the distance. With the following incredibly silly
> constraint:
>
> alter table zzz add constraint
> check( (select sum(zzzz.f1*zzz1.f1_1) from zzzz,zzz1
> where zzzz.f2=zzz1.f1_1) + f1 > 0);
>
> Any updates to the referenced tables cause it to run a query that *seems*
> to be:
>
> select * from ZZZ where not ( <constraint-condition> )
>
> This is my guess based on the optimizer output, but it seems reasonable.
>
> An update to the main table causes the same query to be executed but only
> for the updated row.
>
> Adding the main table to the check query produces a similar result.
>
>
> This may be a useful general model? Is there a problem with the approach?
> It couls be further improved by using 'limit 1', but otherwise seems good...
Well, I'm not sure how easy/hard it is to get all referenced tables right
now, since you'd have to decend into subqueries. But, yeah, that seems
like it would probably work since you probably couldn't currently put
anything in the check constraint that would work there but not in a
subquery context (although i could be wrong about that).
Can you do arbitrary user functions in Dec RDB that access tables and put
those in constraints? I'm not sure what to do about the fact that we can't
actually get the referenced tables inside functions for some cases.