错误代码1215:无法添加外键约束MySQL

I'm having a problem creating a database in MySQL. The error code:'Error code 1215: cannot add foreign key constraint' pops up when i try to implement my changes. I've paid attention to all the necessary things but i can't find the solution.

在MySQL中创建数据库有问题。错误代码:“错误代码1215:当我试图实现我的更改时，不能添加外键约束”。我已经注意到所有必要的东西，但我找不到解决办法。

This error only happened after i added some tables after having made an initial database(which did work), so hopefully i'm not dealing with this problem throughout the whole project.

这个错误只有在我添加了一些表之后才会发生(这确实有效)，所以我希望在整个项目中不会处理这个问题。

Here's a snippet of the code in which the error occurs, the foreign key that's not working correctly is 'tournament_id' referencing to 'id' in tournament:

这是错误发生的代码片段，错误工作的外键是“比赛中”与“id”相关的“比赛中”:

CREATE DATABASE allin;

USE allin;

CREATE TABLE employee (
phone_number       char(12)            NOT NULL,
birth_date         date                NOT NULL,
tournament_id      int                 NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(phone_number),
FOREIGN KEY(tournament_id)             REFERENCES tournament(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Second table:

第二个表:

CREATE TABLE tournament (
id                  int                 NOT NULL AUTO_INCREMENT,
date                date                NOT NULL,
time                time                NOT NULL,
cost                decimal(5,2)        NOT NULL,
min_players         int                 NOT NULL,
min_age             int                 NOT NULL,
max_age             int                 NOT NULL,
location_id         int                 NULL,
winner_id           int                 NULL,
type                varchar(40)         NULL,
PRIMARY KEY(id),
FOREIGN KEY(winner_id)                  REFERENCES player(id) ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY(location_id)                REFERENCES event_location(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2 个解决方案

#1

The issue is here:

这里的问题是:

FOREIGN KEY(tournament_id) REFERENCES tournament(id) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

the above query is of CREATE TABLE employee. In this query, you are creating a FOREIGN KEY that refers to tournament(id), but as of now there is no tournament table exist in the specified database as the tournament table create query is reside below in the sequence.

上面的查询是CREATE TABLE employee。在这个查询中，您正在创建一个外键，该外键引用联赛(id)，但是到目前为止，指定的数据库中不存在联赛表，因为联赛表创建查询位于序列的下面。

I layman terms we can say, you are trying to refer a table column that do not exist.

我们可以说，您试图引用一个不存在的表列。

So to resolve this, run all you parent table creation query first, and than child table.

要解决这个问题，首先运行所有父表创建查询，而不是子表。

#2

tournament_id int NOT NULL AUTO_INCREMENT,

PRIMARY KEY(phone_number)

Hey, I don't think you could set another primary key while an "auto increment" already exist

嘿，我不认为你可以设置另一个主键，而“自动增量”已经存在

#1