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

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