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

On Tue, 27 Sept 2022 at 06:08, James Coleman <jtc331(at)gmail(dot)com> wrote:
>
> On Mon, Sep 26, 2022 at 9:59 AM Wolfgang Walther
> <walther(at)technowledgy(dot)de> wrote:
> > So no need for me to distract this thread from $subject anymore. I think
> > the idea of allowing to create unique constraints on a superset of the
> > columns of an already existing unique index is a good one, so let's
> > discuss this further.
>
> Sounds good to me!

I don't see any immediate problems with allowing UNIQUE constraints to
be supported using a unique index which contains only a subset of
columns that are mentioned in the constraint. There would be a few
things to think about. e.g INSERT ON CONFLICT might need some
attention as a unique constraint can be specified for use as the
arbiter.

Perhaps the patch could be broken down as follows:

0001:

* Extend ALTER TABLE ADD CONSTRAINT UNIQUE syntax to allow a column
list when specifying USING INDEX.
* Add checks to ensure the index in USING INDEX contains only columns
mentioned in the column list.
* Do any required work for INSERT ON CONFLICT. I've not looked at the
code but maybe some adjustments are required for where it gets the
list of columns.
* Address any other places that assume the supporting index contains
all columns of the unique constraint.

0002:

* Adjust transformFkeyCheckAttrs() to have it look at UNIQUE
constraints as well as unique indexes
* Ensure information_schema.referential_constraints view still works correctly.

I think that would address all of Tom's concerns he mentioned in [1].
I wasn't quite sure I understood the NOT NULL concern there since
going by RI_FKey_pk_upd_check_required(), we don't enforce FKs when
the referenced table has a NULL in the FK's columns.

David

[1] https://www.postgresql.org/message-id/3057718.1658949103@sss.pgh.pa.us