Re: Proposed feature: Selective Foreign Keys

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.

Different strokes for different folks, I guess.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company