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)
);