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

Here you have how to relation entities :http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/association-mapping.html, and here symfony docs about mapping : http://symfony.com/doc/current/book/doctrine.html#add-mapping-information — fito, Jan 13 '16 at 16:48
I think it would help if your question were a little more specific in what you are trying to do and contained some code that you might have tried. — Jason Roman, Jan 22 '16 at 15:17
Normally, the `team` table should have only `group_id` and should **not** have `tournament_id` at all. I don't understand why you de-normalize your schema. — Vladimir Baranov, Feb 01 '17 at 11:50
in a scenario where we want to apply constraints on multiple columns a replicated foreign key would be very helpful and actually reduce the amount of relationships and simplify query a lot eg:- we want to make the combination of group_id and tournament_id [from group table] unique so that the same groups are't added to the tournament multiple times — some_groceries, Feb 01 '17 at 18:26

score 5 · Accepted Answer · answered Feb 01 '17 at 11:42

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)

any other ways to achieve this without triggers? OP asked alternatives to triggers — some_groceries, Feb 01 '17 at 18:54
Oh, yes, I haven't read the bounty note. If I saw it I would not post the answer. But ... what's wrong with a trigger? This is the most natural and standard solution in cases like this. — klin, Feb 01 '17 at 19:29

score 3 · Answer 2 · edited May 23 '17 at 12:17

You can just use identifying relationships:

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.

score 1 · Answer 3 · answered Feb 08 '17 at 08:25

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

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

3 Answers3