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
2
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
0
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
2
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
0
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
嘿,我不认为你可以设置另一个主键,而“自动增量”已经存在