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

>>> 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.

I don't think that the UNIQUE NULLS DISTINCT/NOT DISTINCT patch will have
any
impact on this proposal. Currently (and admittedly I haven't thought at all
about MATCH PARTIAL), a NULL in a referencing row precludes a reference at
all:

* If the foreign key constraint is declared MATCH SIMPLE, then no
referenced
row exists for the referencing row.
* If the foreign key constraint is declared MATCH FULL, then the
referencing
row must not have a NULL in any of its referencing columns.

UNIQUE NULLS NOT DISTINCT is the current behavior, and this proposql
shouldn't
have a problem with the current behavior. In the case of UNIQUE NULLS
DISTINCT,
then NULLs behave, from a uniqueness perspective, as a singleton value and
thus
shouldn't cause any additional semantic difficulties in regards to this
proposal.

I don't have access to a copy of the SQL specification and it doesn't look
like
anyone implements MATCH PARTIAL. Based on what I can gather from the
internet,
it appears that MATCH PARTIAL allows at most one referencing column to be
NULL,
and guarantees that at least one row in the referenced table matches the
remaining columns; implicitly, multiple matches are allowed. If these are
the
semantics of MATCH PARTIAL, then it seems to me that uniqueness of the
referenced rows aren't very important.

What other semantics and edge cases regarding this proposal should I
consider?