Search code examples
sqllinqdatabase-designrelationships

Create Relationship Between Two Columns Not Using PK SQL

I have two tables, one named Game and the other GameStats.

Game Design

GameId (PK)
GameGuid
MatchDate
TeamZeroScore
TeamOneScore
Active

GameStats Design

GameStatsId (PK)
GameGuid
TeamId
Name
Kills
Deaths
Assists
Active

A game can have many game stats. So my question is is there any way to set up a relationship between Game and Gamestats using the GameGuid field even though they are not primary keys?

Solution

  • You can, but I wouldn't recommend it. In general, you can set up a foreign key relationship to another table using either a primary key or unique key (where key can actually consist of multiple columns).

    However, you don't want to store redundant data in tables. So, if GameId is the primary key for Game, then the second table should be:

    GameStatsId (PK)
GameId references Game(GameId)
TeamId
Name
Kills
Deaths
Assists
Active

    You can look up the GameGuid by doing a join.

    On the other hand, if you want to use GameGuid as the primary key, then use that and remove GameId.