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(at)dunslane(dot)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