Re: Proposed feature: Selective Foreign Keys

On 4 December 2013 01:24, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Yeah, more or less, but the key is ensuring that it wouldn't let you
> create the constraint in the first place if the partial index
> specified *didn't* match the WHERE clause. For example, suppose the
> partial index says WHERE parent_entity = 'event' but the constraint
> definition is WHERE parent_event = 'somethingelse'. That ought to
> fail, just as creating a regular foreign constraint will fail if
> there's no matching unique index.

The where clause only applies to queries against the FK table, and we
don’t currently fail if there isn’t a matching index on the fk column
when creating a FK (I’ve been bitten by that before).

We fail if there isn’t a unique index on the referenced
table/column(s), but queries against that table on insert/update not
the FK table are unchanged (save that we don’t bother with them at all
if the where clause expression fails for the given tuple).

Cheers

Tom