Search code examples
postgresqldatabase-designone-to-manytable-relationshipspostgresql-9.6

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


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.

table structure

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


Solution

  • 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)