On Mon, Sep 26, 2022 at 10:04 AM Wolfgang Walther <walt...@technowledgy.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
