Re: Allow foreign keys to reference a superset of unique columns

Kaiting Chen <ktchen14(at)gmail(dot)com> writes:
>> Another example is that I think the idea is only well-defined when
>> the subset column(s) are a primary key, or at least all marked NOT NULL.
>> Otherwise they're not as unique as you're claiming. But then the FK
>> constraint really has to be dependent on a PK constraint not just an
>> index definition, since indexes in themselves don't enforce not-nullness.
>> That gets back to Peter's complaint that referring to an index isn't
>> good enough.

> The unique index underlying foo.a guarantees that (foo.a, foo.b) is unique
> if foo.a isn't NULL. That is, there can be multiple rows (NULL, 1) in foo.
> However, such a row can't be the target of the foreign key constraint
> anyway.

You're ignoring the possibility of a MATCH PARTIAL FK constraint.
Admittedly, we don't implement those today, and there hasn't been
a lot of interest in doing so. But they're in the SQL spec so we
should fix that someday.

I also wonder how this all interacts with the UNIQUE NULLS NOT
DISTINCT feature that we just got done implementing for v15.
I don't know if the spec says that an FK depending on such a
constraint should treat nulls as ordinary unique values --- but
it sure seems like that'd be a plausible user expectation.

The bottom line is there's zero chance you'll ever convince me that this
is a good idea. I think the semantics are at best questionable, I think
it will break important optimizations, and I think the chances of
finding ourselves in conflict with some future SQL spec extension are
too high. (Even if you can make the case that this isn't violating the
spec *today*, which I rather doubt so far as the information_schema is
concerned. The fact that we've got legacy behaviors that are outside
the spec there isn't a great argument for adding more.)

Now, if you can persuade the SQL committee that this behavior should be
standardized, then two of those concerns would go away (since I don't
think you'll get squishy semantics past them). But I think submitting
a patch now is way premature and mostly going to waste people's time.

regards, tom lane