On Tue, 27 Sept 2022 at 06:08, James Coleman <jtc...@gmail.com> wrote: > > On Mon, Sep 26, 2022 at 9:59 AM Wolfgang Walther > <walt...@technowledgy.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.1658949...@sss.pgh.pa.us
