竞争表外键中存在错误

Question

我一直收到一个关于表"compare"中外键的错误。我一直在努力找出问题所在，但似乎没有一个例子是我的问题。有人能帮我吗？这是我不断得到的错误：

ERROR 1005 (HY000): Can't create table `database`.`compete` (errno: 150 "Foreign key constraint is incorrectly formed")

这是我的密码。

CREATE TABLE athlete (
athlete_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
country TINYTEXT NOT NULL,
name TINYTEXT NOT NULL,
birthdate DATE NOT NULL,
age INT UNSIGNED NOT NULL,
height_inch INT UNSIGNED,
weight_lbs INT UNSIGNED,
PRIMARY KEY (athlete_id)
);

CREATE TABLE sport (
sport_id INT UNSIGNED NOT NULL,
sport TINYTEXT NOT NULL,
PRIMARY KEY (sport_id)
);

CREATE TABLE olympics (
olympics_id INT UNSIGNED NOT NULL,
season TINYTEXT NOT NULL,
year YEAR NOT NULL,
city TINYTEXT NOT NULL,
PRIMARY KEY (olympics_id)
);  

CREATE TABLE sport_events (
sport_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
event TINYTEXT NOT NULL,
PRIMARY KEY (sport_id, event_id),
FOREIGN KEY (sport_id) REFERENCES sport (sport_id)
);

CREATE TABLE athlete_sport (
athlete_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
sport_id INT UNSIGNED NOT NULL,
PRIMARY KEY (athlete_id),
FOREIGN KEY (athlete_id) REFERENCES athlete (athlete_id),
FOREIGN KEY (sport_id) REFERENCES sport (sport_id)
);

CREATE TABLE compete (
athlete_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
olympics_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
medal TINYTEXT,
PRIMARY KEY (athlete_id),
FOREIGN KEY (athlete_id) REFERENCES athlete (athlete_id),
FOREIGN KEY (olympics_id) REFERENCES olympics (olympics_id),
FOREIGN KEY (event_id) REFERENCES sport_events (event_id)   
);

Answer 1

删除并重新创建sport_events表，并使用event_id列作为其主键的第一列。

CREATE TABLE sport_events (
sport_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
event TINYTEXT NOT NULL,
PRIMARY KEY (event_id,sport_id),
FOREIGN KEY (sport_id) REFERENCES sport (sport_id)
);

Answer 2

在sport_events表中，您已经声明了具有两列的主键。PRIMARY KEY (sport_id, event_id)

但在compete表中，您添加了带有单列的FOREIGN KEY (event_id) REFERENCES sport_events (event_id)。

这就是为什么你errno: 150 "Foreign key constraint is incorrectly formed"错误。

先删除complete表，然后再删除sport_events表。然后更改sport_events表中的主键PRIMARY KEY (event_id)。

或者，只需切换PRIMARY KEY (event_id, sport_id)。这会奏效的。

之后创建两个表。