CODEWITHSUNDEEP
postgresqldatabase-designone-to-manytable-relationshipspostgresql-9.6
pinch boi triggered af
pinch boi triggered af

Reputation: 544

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

Upvotes: 13

Views: 1199

Answers (3)

LoG
LoG

Reputation: 1141

You can use a SELECT to feed a value with INSERT or UPDATE:

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

This is what I am using to let Postgres insert ids for foreign keys without the (unconsistent) double query (select from groups, insert into teams).

Upvotes: 1

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

You can just use identifying relationships:

enter image description here

This produces keys which "propagate" all the way down, so you get team.tournament_id which is guaranteed to point to the same tournament as the parent group.

Note that I used group_no (not group_id), as a naming convention to indicate it doesn't identify the group alone, but in combination with the tournament_id. Ditto for team_no.

This complicates insertion, though. You can no longer just let the database generate the next auto-incremented ID for your group or team (because you are now identifying it with a combination of values, not just one value), but you can easily do it manually - e.g. when inserting into group, assign SELECT MAX(group_no) + 1 FROM group WHERE tournament_id = ... to the new group_no, and similar for the team.

BTW, this can be done in addition to surrogate keys (the id in your diagram), if you still need them.

Upvotes: 3

klin
klin

Reputation: 121774

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)

Upvotes: 5

Related Questions