How to reference a row which has two foreign keys as its primary key

constraintforeign keyprimary-keysqlite

I am currently trying to setup a database managing players and teams for a competition. Players are competing in teams of two but may be shuffled.

As every team combination can only exist once, I decided that the primary key of each team is composed of the two ids of the players in it:

CREATE TABLE teams (
  player1 INTEGER REFERENCES User(id) NOT NULL,
  player2 INTEGER REFERENCES User(id) NOT NULL,
  rating INTEGER NOT NULL,
  PRIMARY KEY (player1, player2)
);

The competition consists of matches, each match being three games.

I want to reference two teams in a match:

CREATE TABLE matches (
  id INTEGER PRIMARY KEY NOT NULL,
  team_1 INTEGER REFERENCES Team(id) NOT NULL,
  team_2 INTEGER REFERENCES Team(id) NOT NULL,
  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
)

Each game contains the score of the winning and the losing team:

CREATE TABLE games(
  id INTEGER PRIMARY KEY NOT NULL,
  match_id: INTEGER REFERENCES Match(id) NOT NULL,
  score_winner : INTEGER NOT NULL,
  score_loser : INTEGER NOT NULL
)

As I do not know the winner of a game like this, I would like to add the primary key of the team that won the game to be able to to figure out the winner of a match (whoever won the most matches)/

How do I reference the winner of a game in the given table?

Best Answer

What I would do here is to have a table match_winner! It will be a JOINing table (also more formally called an ASSOCIATIVE ENTITY as well as many other names from the page).

I'm not quite sure why you have two tables called matches and games - are they not the same thing? If not, let me know and I'll modify the schema.

Something like this is what you require IMHO. It is a joining table between teams and matches

CREATE TABLE match_winner
(
  match_id INTEGER NOT NULL,
  team_id  INTEGER NOT NULL,
  winning_score INTEGER NOT NULL CHECK (winning_score > 0),

  CONSTRAINT 'match_winner_pk' PRIMARY KEY (match_id, team_id),
  CONSTRAINT 'mw_match_fk'     FOREIGN KEY (match_id) REFERENCES matches (match_id),
  CONSTRAINT 'mw_team_fk'      FOREIGN KEY (team_id)  REFERENCES teams   (team_id) 
);

I made a small modification to your schema - I put a id INTEGER SERIAL into the teams table definition - check out the fiddle here. SERIALs are great! I also added a constraint CHECK (player1 != player2).

I then ran this SQL

SELECT t.player1, t.player2, m.id, mw.match_id, mw.team_id, mw.winning_score
FROM teams t
JOIN matches m ON t.player1 = m.team_1 AND t.player2 = m.team_2
JOIN match_winner mw ON m.id = mw.match_id;

You can check the result on the fiddle. Having the joining table avoids the use of NULL and provides added flexibility. Just a couple of recommendations - use upper case for SQL keywords and lower case for your own variables - or the opposite - just choose a standard and stick to it. You should also name your constraints as I did for the match_winner table - it's much easier to figure out error messages that way.

I hope this answers your question - if not, let me know! p.s. welcome to the forum! ;-)

Ooops - I nearly didn't notice the question. I reversed my modification of your teams schema - see the fiddle here, but the SQL is exactly the same. To use a two field PRIMARY KEY you just join on both fields instead of just a simple id one - et voilà! Of course, you can have the id and with a UNIQUE constraint, it's that bit easier sometimes - it's just that I wanted to give you the identites of the players. I've added a further fiddle here showing how you can alias a table twice in the same query to do useful things - here to join the names of player1 and player2 to your results.

Related Question