Обсуждение: Partial foreign keys

Hello,

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?

Thanks.

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.

rihad <rihad@mail.ru> writes:
> 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?

No.  To take just the most obvious example, what happens when somebody
modifies or deletes the referenced row?  Your trigger won't handle that
case because it won't be called.

In principle you could get most of the way there if you also hung an
ON UPDATE/DELETE trigger on the referenced table and had it do the
appropriate things.  But even so, there would be race conditions that
are really insoluble in straightforward user triggers.  The built-in
support for FK checking relies on triggers in these places, but the
triggers access some functionality that's not available at the SQL level
in order to handle concurrent updates correctly.

My advice is to rethink your data layout so that you don't need this.
One possibility is to keep the "flag=false" and "flag=true" rows in
two different tables, which could be made to look like one table
(at least for non-updating queries) via a view or inheritance.

            regards, tom lane