多对多关系的 CREATE TABLE 上的 MySQL 错误

问题描述

我有一个简单的 CREATE TABLE 查询的问题.我有两个表"度假村"(带有度假村 ID 和度假村名称)和季节"(带有度假村 ID 和度假村名称)处于多对多关系.我正在尝试使用以下两个查询中的任何一个创建连接表 Resort_season:

I have an issue with a simple CREATE TABLE query. I have two tables" 'resort' (with resortID and resortName) and 'season' (with resortID and resortName) in a many to many relationship.I am trying to create the joining table resort_season 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
);

两个查询都给我一个错误，内容如下(在本例中的第二个查询的情况下):您的语法有错误；请检查手册......以获取在第 2 行附近使用'FOREIGN KEY REFERENCES 度假村(resortID)，季节ID MEDIUMINT UNSIGNED NOT NULL F'的正确语法".

Both queries give me an error that reads (in the case of the second query 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 can not see why the error.

如果我只是这样做

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 am not sure though if this table will validly reference the tables resort and season.

推荐答案

从主键定义中移除 NOT NULL -

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

这篇关于多对多关系的 CREATE TABLE 上的 MySQL 错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！