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

On Mon, Sep 26, 2022 at 10:04 AM Wolfgang Walther
<walther(at)technowledgy(dot)de> wrote:
>
> James Coleman:
> > As I was reading through the email chain I had this thought: could you
> > get the same benefit (or 90% of it anyway) by instead allowing the
> > creation of a uniqueness constraint that contains more columns than
> > the index backing it? So long as the index backing it still guaranteed
> > the uniqueness on a subset of columns that would seem to be safe.
> >
> > Tom notes the additional columns being nullable is something to think
> > about. But if we go the route of allowing unique constraints with
> > backing indexes having a subset of columns from the constraint I don't
> > think the nullability is an issue since it's already the case that a
> > unique constraint can be declared on columns that are nullable. Indeed
> > it's also the case that we already support a foreign key constraint
> > backed by a unique constraint including nullable columns.
> >
> > Because such an approach would, I believe, avoid changing the foreign
> > key code (or semantics) at all, I believe that would address Tom's
> > concerns about information_schema and fuzziness of semantics.
>
>
> Could we create this additional unique constraint implicitly, when using
> FOREIGN KEY ... REFERENCES on a superset of unique columns? This would
> make it easier to use, but still give proper information_schema output.

Possibly. It'd be my preference to discuss that as a second patch
(could be in the same series); my intuition is that it'd be easier to
get agreement on the first part first, but of course I could be wrong
(if some committer, for example, thought the feature only made sense
as an implicit creation of such a constraint to back the use case
Kaiting opened with).

James Coleman