> > In that case it makes absolutely no difference, does constraint work in
> > system context or not. What a difference - would it be violated by
> > changes done, but not commited yet, or a bit later in the future.
>
> create table master (pk int not null primary key);
> create table detail (fk int);
>
> alter table detail add check (fk in ( select pk from master )); -- nulls

are

> also allowed
> alter table master add check (not exists ( select 1 from detail left join
> master on pk = fk where fk is not null ));
>
> If our implementation would be SQL-compliant, so:
>
> 1) constraint is evaluated per statement instead of per row
> 2) constraint runs in system context
> 3) constraint is fired on delete
>
> then the above example would implement referential integrity, but without
> indices. This cannot be done via triggers, and index-based FKs are evil in
> some cases.

if index-based FKs are evil, then this should be fixed :-)

But I believe the new index structure changes this already.

> P.S. This is an idea only, not a working example :-)

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com