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