Re: Proposed feature: Selective Foreign Keys

On 12/04/2013 12:00 PM, Robert Haas wrote:
> On Wed, Dec 4, 2013 at 11:44 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
>>> 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.
> Interestingly, the variant for which you can't think of a use case is
> the one I've missed most.  Typical examples in my experience are
> things like project.project_manager_id references person (id) where
> person.is_project_manager, or alert (device_id) references device (id)
> where not device.deleted.
>

OK, those make sense. I wonder whether this should be done via a USING 
clause on the constraint that pointed to the partial unique index. Or 
would that be too obscure?

cheers

andrew