How to replicate foreign key of another table in one to many relationship

Question

I have a three table structure: tournament, group and team. The tournament and group tables have a one-to-many relation, and group and team have a one-to-many relation as shown below.

How do i replicate the value of the tournament_id from group table into the group_tournament_id of team table?

i'm looking for an answer which will achieve this using the create statement like

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

of course this would not work because in order to reference something it has to be unique, in this case tournament_id is not unique

i need a standard way to copy over the value of tournament_id from group into 'team' table's group_tournament_id when ever i insert group_id inside team table

edit: no longer need answer in symfony, just postgreSQL would be fine

Answer 1

You should use the reference directly from teams to tournaments. Use a trigger to automatically obtain the appropriate reference from groups. (Note however that the reference is not necessary as you can always get tournament_id from groups in a query. I assume that this reference is to simplify some queries).

I've slightly modified the names of tables and columns (group cannot be a name of a table).

Tables:

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();

Test:

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)