Как копировать внешний ключ другой таблицы в отношениях от одного до другого

У меня есть три структуры таблицы: tournament, group и team. Таблицы tournament и group имеют отношение один ко многим, а group и team имеют отношение one-to-many, как показано ниже.

Как реплицировать значение таблицы tournament_id из group в таблицу group_tournament_id из team?

Я ищу ответ, который достигнет этого, используя оператор create, например

create table team (
    id serial primary key, 
    group_id int references group, 
    group_tournament_id int references group(tournament_id)
);

конечно, это не сработало бы, потому что для того, чтобы ссылаться на что-то, оно должно быть уникальным, в этом случае турнир_ид не уникален

Мне нужен стандартный способ скопировать значение tournament_id из group в таблицу "team" group_tournament_id, когда я вставляю group_id inside команду table

edit: больше не нужно отвечать в symfony, просто postgreSQL будет хорошо

Ответ 1

Вы должны использовать ссылку непосредственно от teams до tournaments. Используйте триггер, чтобы автоматически получить соответствующую ссылку из groups. (обратите внимание, однако, что ссылка не нужна, поскольку вы всегда можете получить tournament_id от groups в запросе. Я предполагаю, что эта ссылка упрощает некоторые запросы).

Я немного изменил имена таблиц и столбцов (group не может быть именем таблицы).

Таблицы:

create table tournaments (
    tournament_id serial primary key);

create table groups (
    group_id serial primary key,
    tournament_id int references tournaments);

create table teams (
    team_id serial primary key, 
    group_id int references groups, 
    tournament_id int references tournaments);

Trigger:

create or replace function before_insert_or_update_on_teams()
returns trigger language plpgsql as $$
begin
    new.tournament_id = (
        select tournament_id
        from groups
        where group_id = new.group_id
        limit 1);
    return new;
end $$;

create trigger before_insert_or_update_on_teams
before insert or update on teams
for each row execute procedure before_insert_or_update_on_teams();

Тест:

insert into tournaments values (default), (default);
insert into groups values (default, 2);
insert into teams values (default, 1, null);

select * from teams;

 team_id | group_id | tournament_id 
---------+----------+---------------
       1 |        1 |             2
(1 row)

Ответ 2

Вы можете просто использовать идентифицирующие отношения:

Это создает ключи, которые "распространяются" полностью вниз, поэтому вы получаете team.tournament_id, который, как гарантируется, указывает на тот же турнир, что и родительская группа.

Обратите внимание, что я использовал group_no (not group_id), поскольку соглашение об именах, указывающее, что оно не идентифицирует группу в одиночку, но в сочетании с tournament_id. То же самое для team_no.

Это усложняет установку. Вы больше не можете просто дать базе данных генерировать следующий автоматически увеличиваемый идентификатор для вашей группы или команды (потому что теперь вы идентифицируете его с комбинацией значений, а не только с одним значением), но вы можете легко сделать это вручную - например, при вставке в group присвойте SELECT MAX(group_no) + 1 FROM group WHERE tournament_id = ... новому group_no и похожим для команды.

Кстати, это можно сделать в дополнение к суррогатным ключам (id на диаграмме), если они вам все еще нужны.

Ответ 3

Вы можете использовать SELECT для подачи значения с помощью INSERT или UPDATE:

INSERT INTO "teams" ("group_id", "tournament_id")
SELECT $1, "groups"."tournament_id"
  FROM "groups" WHERE ("groups"."id" = $1)
RETURNING "id"

Это то, что я использую, чтобы позволить Postgres вставлять идентификаторы для внешних ключей без (несовместимого) двойного запроса (выбирать из групп, вставлять в команды).