What would be the best Foreign and Primary Key for these two tables in SQL. What

Question

What would be the best Foreign and Primary Key for these two tables in SQL. What makes them unique is the combination of the GameTitle and the GameSystem. So I think it would have to be something like this but i don't know how to implement it with both items (should i have both? the tables dont have to be connected)

CONSTRAINT PK_Something PRIMARY KEY (GameTitle,GameSystem)

For my purposes, GameTitle and GameSystem need to be included in both tables. Could you please add it into these tables? Many thanks!

CREATE TABLE GameDetails(
Category Varchar,
GameTitle Varchar,
GameSystem Varchar,
Author Varchar,
GameType Varchar,

HowManyPlayers Int

);

CREATE TABLE GamePurchase(
GameID INT IDENTITY(1,1),
GameTitle Varchar,
GameSystem Varchar,
StatusofGame VARCHAR(16),
BoughtBy VARCHAR(7),
);

Explanation / Answer

As GameDetails can be uniquely identified by (GameTitle , GameSystem) pair , it can be considered as primary key in GameDetails table.

It is Composite Primry Key.

CREATE TABLE GameDetails(
Category Varchar,
GameTitle Varchar,
GameSystem Varchar,
Author Varchar,
GameType Varchar,

HowManyPlayers Int ,

);

------------------------------------------

In GamePurchase table each Game must be having Unique ID. So make GameId as Primary key in GamePurchase table.

For accessing GameTitle and GameSystem reference the GameDetails table via Foreign key.

CREATE TABLE GamePurchase(
GameID INT IDENTITY(1,1),
GameTitle Varchar,
GameSystem Varchar,
StatusofGame VARCHAR(16),
BoughtBy VARCHAR(7),

PRIMARY KEY(GameID),

);

If you want to make (GameTitle,GameSystem) as primary key in GamePurchase table then their should be one-to-one relationship between two tables.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.