"Alex Peshkov" <pes@...> wrote:

>
> 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.

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


Dmitry