Re: Partial foreign keys

[rihad <rihad(at)mail(dot)ru>]

rihad wrote:
> Due to lack of support for partial (conditional) multi-column foreign 
> keys in 8.3, can before-triggers be used to implement them in terms of 
> data consistency and speed?

Let me clarify the question in semi-pseudo-SQL:

table foo {
  bar_id int not null;
  baz_id int not null;
  flag bool;
  key (bar_id, baz_id) references (bar.id, bar.baz_id);
}

table bar {
  id int primary key;
  baz_id int not null;
}


I want the effects of the above foo.key in every sense, but only for 
entries having foo.flag=true. So I think I'll write before-statement 
triggers to do just that instead of the key. But is data consistency 
still guaranteed as the foreign key in foo would otherwise do? What if, 
after the first query trigger checked to see that (foo.bar_id, 
foo.baz_id) multikey exists in bar, another query modifies bar in 
between, and the first query ends up with the wrong assumption? Similar 
problem goes for the table bar's trigger checking that nothing in foo 
still refers to the old column tuple.