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

Поиск
Список
Период
Сортировка
От Jack Christensen
Тема Re: Allow composite foreign keys to reference a superset of unique constraint columns?
Дата
Msg-id CAMovtNrj_b5O4SnsFfuX602EMNc3u9-oOjj6T4CJbiHrXO451Q@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Allow composite foreign keys to reference a superset of unique constraint columns?  ("David G. Johnston" <david.g.johnston@gmail.com>)
Ответы Re: Allow composite foreign keys to reference a superset of unique constraint columns?
Список pgsql-hackers
Дерево обсуждения
On Mon, Aug 16, 2021 at 7:01 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, Aug 16, 2021 at 4:37 PM Paul Martinez <hellopfm@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

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: PG14: Avoid checking output-buffer-length for every encoded byte during pg_hex_encode
Следующее
От: Robert Haas
Дата:
Сообщение: preserving db/ts/relfilenode OIDs across pg_upgrade (was Re: storing an explicit nonce)