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

On Mon, Aug 16, 2021 at 7:01 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Mon, Aug 16, 2021 at 4:37 PM Paul Martinez <hellopfm(at)gmail(dot)com> wrote:
>
>>
>> It seems like a somewhat useful feature. If people think it would be
>> useful to
>> implement, I might take a stab at it when I have time.
>>
>>
> This doesn't seem useful enough for us to be the only implementation to go
> above and beyond the SQL Standard's specification for the references
> feature (I assume that is what this proposal suggests).
>
> This example does a good job of explaining but its assumptions aren't that
> impactful and thus isn't that good at inducing desirability.
>
>

I have no opinion on the broader concerns about this proposed feature, but
speaking simply as a user I have wanted this on multiple occasions. In my
case, it is usually because of the need to maintain consistency in a
diamond table relationship. For example:

create table tenants (
id serial primary key
);

create table users (
id serial primary key,
tenant_id int references tenants
);

create table user_groups (
id serial primary key,
tenant_id int references tenants
);

create table user_group_memberships (
tenant_id int,
user_id int,
user_group_id,
primary key (user_id, user_group_id),
foreign key (user_id, tenant_id) references users (id, tenant_id),
foreign key (user_group_id, tenant_id) references user_groups (id,
tenant_id)
);

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.

Jack