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.

+3


source to share


1 answer


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

      

+1


source







All Articles