多对多关系的 CREATE TABLE 上的 MySQL 错误 [英] MySQL error on CREATE TABLE for many-to-many relationship
问题描述
我有一个简单的 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 错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!