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

On Tue, 2021-08-17 at 10:45 -0700, Paul Martinez wrote:
> On Tue, Aug 17, 2021 at 8:41 AM Jack Christensen <jack(at)jncsoftware(dot)com> wrote:
> > The only way to ensure a user can only be a member of a group in the same
> > tenant is to user_group_memberships.tenant_id be part of the foreign key. And
> > that will only work with a unique key on id and tenant_id in both users and
> > user_groups. It's a bit inelegant to create multiple extra indexes to ensure
> > consistency when existing indexes are enough to ensure uniqueness.
>
> You could accomplish this by using composite primary keys on the users and
> user_groups tables:
>
> CREATE TABLE users (
> id serial,
> tenant_id int REFERENCES tenants(id),
> PRIMARY KEY (tenant_id, id)
> );

That is not a proper solution, because it does not guarantee uniqueness of
the "id" column, which is typically what you want.

So I think Jack's example illustrates the benefit of this proposal well.

On the other hand, the SQL standard requires that a foreign key references
a unique constraint, see chapter 11.8 <referential constraint definition>,
Syntax Rules 3) a):

"If the <referenced table and columns> specifies a <reference column list>,
then there shall be a one-to-one correspondence between the set of
<column name>s contained in that <reference column list> and the set of
<column name>s contained in the <unique column list> of a unique constraint
of the referenced table such that corresponding <column name>s are equivalent."

So while I personally agree that the proposed feature is useful, I am not
sure if it is useful enough to break the standard in a way that may be
incompatible with future extensions of the standard.

Yours,
Laurenz Albe