Subject Re: [Firebird-Architect] Re: Nulls in CHECK Constraints
Author Martijn Tonies
> > 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
> 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
Upscene Productions
Database development questions? Check the forum!