MySQL error in CREATE TABLE for many-to-many relationship
I have a problem with a simple CREATE TABLE query. I have two tables "resort" (with resortID and resortName) and "season" (with resortID and resortName) in many ways. I am trying to create a resort_season join table with either of the two queries below:
CREATE TABLE resort_season (
resortID MEDIUMINT UNSIGNED NOT NULL,
seasonID MEDIUMINT UNSIGNED NOT NULL,
FOREIGN KEY (resortID) REFERENCES resort (resortID),
FOREIGN KEY (seasonID) REFERENCES season (seasonID),
PRIMARY KEY (resortID, seasonID) NOT NULL
);
CREATE TABLE resort_season (
resortID MEDIUMINT UNSIGNED NOT NULL FOREIGN KEY REFERENCES resort (resortID),
seasonID MEDIUMINT UNSIGNED NOT NULL FOREIGN KEY REFERENCES season (seasonID),
PRIMARY KEY (resortID, seasonID) NOT NULL
);
Both queries give me an error that reads (in the case of the second request, in this case)
"You have an error in your syntax; check manual....for right syntax to use near'FOREIGN KEY REFERENCES resort (resortID), seasonID MEDIUMINT UNSIGNED NOT NULL F' at line 2"
.
What am I doing wrong? This is giving me a headache because I just don't understand why the error is.
If I just
CREATE TABLE resort_season (
resortID MEDIUMINT UNSIGNED NOT NULL,
seasonID MEDIUMINT UNSIGNED NOT NULL,
PRIMARY KEY (resortID, seasonID) NOT NULL
);
then it works fine and the table is created. I'm not sure though if this table will actually refer to the resort and season tables.
source to share
Remove NOT NULL from the primary key definition -
CREATE TABLE resort_season(
resortID MEDIUMINT UNSIGNED NOT NULL,
seasonID MEDIUMINT UNSIGNED NOT NULL,
FOREIGN KEY (resortID) REFERENCES resort (resortID),
FOREIGN KEY (seasonID) REFERENCES season (seasonID),
PRIMARY KEY (resortID, seasonID)
);
source to share