0

I have the following tables:

CREATE TABLE player (
playerID    INT,
name        VARCHAR(255) NOT NULL,
gender      CHAR(1) CHECK (gender IN ('M','F')) NOT NULL,
dob         DATE NOT NULL,
address     VARCHAR(255),
telNo       VARCHAR(20),
NHSNo       VARCHAR(12),
PRIMARY KEY (playerID),
UNIQUE (NHSNo)
);

CREATE TABLE stadium (
stadiumID   INT,
name        VARCHAR(255) NOT NULL,
address     VARCHAR(255) NOT NULL,
telNo       VARCHAR(20) NOT NULL,
PRIMARY KEY (stadiumID)
);

CREATE TABLE tournament (
onDate      DATE,
result      VARCHAR(4) CHECK (result IN ('Win','Lose')),
stadiumID   INT NOT NULL,
PRIMARY KEY (onDate, stadiumID),
FOREIGN KEY (stadiumID) REFERENCES stadium (stadiumID),
UNIQUE (onDate, coachID)
);

Whenever I execute the table below, it's giving the error. :( I do not see what is wrong with it.

CREATE TABLE playTour (
playerID    INT NOT NULL,
onDate      DATE NOT NULL,
stadiumID   INT NOT NULL,
PRIMARY KEY (playerID, onDate, stadiumID),
FOREIGN KEY (playerID) REFERENCES player (playerID),
FOREIGN KEY (stadiumID) REFERENCES stadium (stadiumID),
FOREIGN KEY (onDate) REFERENCES tournament (onDate)
);

It's this bit that's causing it:

FOREIGN KEY (onDate) REFERENCES tournament (onDate)
);

0 Answers0