Re: Proposed feature: Selective Foreign Keys

On 12/04/2013 11:25 AM, Robert Haas wrote:
> On Tue, Dec 3, 2013 at 5:57 PM, Tom Dunstan <pgsql(at)tomd(dot)cc> wrote:
>> 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).
> Oh. I misinterpreted what this feature was about, then. I thought it
> was about restricting the reference to a subset of the *referenced*
> table, but it seems to be about restricting the constraint to a subset
> of the *referencing* table. I guess they're both useful, but the
> syntax...
>
> REFERENCES tab(col) WHERE (stuff)
>
> ...sure looks like the WHERE clause is syntactically associated with
> the table being referenced. What would we do if we eventually wanted
> to support both variants?
>

Well I guess we could say something like:

FOREIGN KEY (a-col) WHERE (a-condition) REFERENCES b(b-col) WHERE
(b-condition)

But it's somewhat ugly.

The case of restricting the allowed referent rows does look slightly
like a solution in search of a problem, but maybe that's just because I
haven't thought of a use for it yet.

cheers

andrew