How would I create a Foreign and Primary Key for these two tables in SQL. What m

Question

How would I create a 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

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

Adding Primary Key

It can be done in two ways: -

1. If you want to have it in create statement itself then it will be like below: -

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

HowManyPlayers Int,

CONSTRAINT PK_Something PRIMARY KEY (GameTitle,GameSystem)

);

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

CONSTRAINT PK_Something1 PRIMARY KEY (GameTitle,GameSystem)
);

2. If you want to add it after create table commands are executed i.e. tables are already there, you now need to add primary key : -

ALTER TABLE GameDetails
ADD CONSTRAINT PK_Something PRIMARY KEY (GameTitle,GameSystem);

ALTER TABLE GamePurchase
ADD CONSTRAINT PK_Something1 PRIMARY KEY (GameTitle,GameSystem);

2. After Adding Primary key lets see how to add FOREIGN Key. Suppose here we want to add foreign key of GameTitle and GameSystem in GamePurpose table and reference to GameDetails table. This can also be done in two ways: -

1. Changing create statement

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

CONSTRAINT PK_Something1 PRIMARY KEY (GameTitle,GameSystem),

CONSTRAINT FK_Something FOREIGN KEY (GameTitle)
    REFERENCES GameDetails(GameTitle),

CONSTRAINT FK_Something1 FOREIGN KEY (GameSystem)
    REFERENCES GameDetails(GameSystem)
);

2. Using Alter statements.

ALTER TABLE GameDetails
ADD CONSTRAINT FK_Something FOREIGN KEY (GameTitle)
    REFERENCES GameDetails(GameTitle);

ALTER TABLE GameDetails
ADD CONSTRAINT FK_Something1 FOREIGN KEY (GameSystem)
    REFERENCES GameDetails(GameSystem );

Hope it is clear, let me know if you want something else in this.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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