On Mon, Aug 16, 2021 at 7:01 PM David G. Johnston < david.g.johns...@gmail.com> wrote:
> On Mon, Aug 16, 2021 at 4:37 PM Paul Martinez <hello...@gmail.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
