Foreign Key References Two Tables

Question

I'm currently working on a database and I came across a new problem to me. The entities involved are Universe, Competition, Game, Pot. Here are the SQL files to create the tables:

CREATE TABLE Universe (
    id int NOT NULL IDENTITY PRIMARY KEY,
    history nvarchar (max),
    creation_date date
);

CREATE TABLE Pot (
    pot_name nvarchar(100),
    universe_id int FOREIGN KEY REFERENCES Universe(id),
    pot_description nvarchar(100),
    media_description nvarchar(100),
    is_official_pot bit
    PRIMARY KEY (pot_name, universe_id)
);

CREATE TABLE Competition (
    universe_id int NOT NULL FOREIGN KEY REFERENCES Universe(id),
    compt_name nvarchar(100) NOT NULL,
    alias nvarchar(100),
    history nvarchar(max),
    rules nvarchar (max),
    winner_id nvarchar(100) FOREIGN KEY REFERENCES RaulUser(username),
    edition int NOT NULL,
    is_official_competition bit NOT NULL,
    PRIMARY KEY (universe_id, compt_name, edition)
);

CREATE TABLE Game (
    id int NOT NULL IDENTITY PRIMARY KEY,
    pot_name nvarchar (100) NOT NULL,
    universe_id int NOT NULL,
    competition_name nvarchar(100) NOT NULL, 
    competition_edition int NOT NULL,
    competition_round int NOT NULL,
    home_raul_u_username nvarchar (100) FOREIGN KEY REFERENCES RaulUser(username) NOT NULL,
    home_team nvarchar (100) NOT NULL FOREIGN KEY REFERENCES Team(team_name),
    home_score  int,
    away_raul_u_username nvarchar (100) NOT NULL FOREIGN KEY REFERENCES RaulUser(username),
    away_team nvarchar (100) NOT NULL FOREIGN KEY REFERENCES Team(team_name),
    away_score  int,
    is_over bit NOT NULL,
    played_date date,
    FOREIGN KEY (universe_id, competition_name, competition_edition) REFERENCES Competition(universe_id, compt_name, edition),
    FOREIGN KEY (universe_id, pot_name) REFERENCES Pot(universe_id, pot_name)
);

The problem starts with this last table (Game), as I can't use universe_id as a Foreign Key for different tables. What's the best approach to solving this? Creating an M:M table Game_Pot? I only need to record the Pot of each Game because Pots change overtime and I don't want to lose that data.

Sorry for the long post and thank you all in advance :)

Does this help? https://stackoverflow.com/questions/7844460/foreign-key-to-multiple-tables — Will, Aug 29 '20 at 12:15
MySQL does not support `IDENTITY`. Maybe you are running SQL Server? — GMB, Aug 29 '20 at 12:17
@Will thank you for the link! I had already checked that post but it addresses a different aspect. In that case, the Foreign key that references Multiple Tables can EITHER be referring on or the other. In my case it is always referring both tables. — lguilhermef, Aug 30 '20 at 10:41
@GMB I'm running a SQL Server, yes, and managing it with MSSMS! — lguilhermef, Aug 30 '20 at 10:41

GMB · Accepted Answer · 2020-08-29T12:24:21.570

1

The only problem that I see is in the definition of table Game:

FOREIGN KEY (universe_id, pot_name) REFERENCES Pot(universe_id, pot_name)

Ordering of columns matters. The primary key of table Pot is (pot_name, universe_id), so you need to swap the columns in the foreign key, like so:

FOREIGN KEY (pot_name, universe_id) REFERENCES Pot(pot_name, universe_id)

Note that having identity (or the-like) primary key in every table might simplify your design: it would allow you to reduce the number of columns in the children tables, and to use single-column foreign keys. Meanwhile, you can still enforce uniqeness on columns tuples in the parent tables with unique constraints.

edited Aug 29 '20 at 12:24

answered Aug 29 '20 at 12:19

GMB

216,147
25
84
135

Thank you so much :) The problem was about the Ordering like you said. I didn't know it is important! Lesson learned! Regarding the use of an Id: shouldn't I avoid using an ID when I have a natural key, although composed? In some other tables, I do use IDs but I try to reduce its use to those cases where it is absolutely needed. Where can I learn more about this? – lguilhermef Aug 30 '20 at 10:47

Foreign Key References Two Tables

1 Answers1