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

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

структура таблицы

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

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

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

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

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

редактировать: больше не нужен ответ в symfony, просто postgreSQL будет в порядке


postgresql database-design one-to-many postgresql-9.6 table-relationships
person pinch boi triggered af    schedule 13.01.2016    source источник
comment
Здесь вы узнаете, как связывать сущности: Doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/, а здесь документация по symfony по отображению: symfony.com/doc/current/book/   -  person fito    schedule 13.01.2016
comment
Я думаю, было бы полезно, если бы ваш вопрос был немного более конкретным в отношении того, что вы пытаетесь сделать, и содержал некоторый код, который вы, возможно, пробовали.   -  person Jason Roman    schedule 22.01.2016
comment
Обычно таблица team должна содержать только group_id и не вообще иметь tournament_id. Я не понимаю, почему вы денормализуете свою схему.   -  person Vladimir Baranov    schedule 01.02.2017
comment
в сценарии, где мы хотим применить ограничения к нескольким столбцам, реплицированный внешний ключ был бы очень полезен и фактически уменьшил количество отношений и значительно упростил запрос, например: - мы хотим сделать комбинацию group_id и tour_id [из групповой таблицы] уникальным, чтобы одни и те же группы не добавлялись в турнир несколько раз   -  person Dheeraj    schedule 01.02.2017

Ответы (3)


arrow_upward
5
arrow_downward

Вы должны использовать ссылку непосредственно от 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);

Триггер:

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)
person klin    schedule 01.02.2017
comment
любые другие способы добиться этого без триггеров? ОП спросил альтернативы триггерам - person Dheeraj; 01.02.2017
comment
О, да, я не читал записку о награде. Если бы я это увидел, я бы не публиковал ответ. Но... что не так с триггером? Это самое естественное и стандартное решение в подобных случаях. - person klin; 01.02.2017

arrow_upward
3
arrow_downward

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

введите здесь описание изображения

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

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

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

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

person Branko Dimitrijevic    schedule 05.02.2017

arrow_upward
1
arrow_downward

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

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

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

person LoG    schedule 08.02.2017