Error code 1215: Can't add MySQL foreign key constraint
I am having a problem creating a database in MySQL. Error code: "Error code 1215: Cannot add foreign key constraint" appears when I try to implement my changes. I have paid attention to all the necessary things, but I cannot find a solution.
This error only happened after I added a few tables after creating the original database (which actually worked), so hopefully I am not dealing with this issue throughout the project.
Here is the code snippet where the error occurs, the foreign key that is not working correctly is tournament_id referencing the "id" in the tournament:
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;
source to share
The problem is here:
FOREIGN KEY(tournament_id) REFERENCES tournament(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
the above query matters CREATE TABLE employee
. This query creates the FOREIGN KEY
one that references tournament(id)
, but there is no table in the specified database at this time tournament
because the create table query tournament
is lower in the sequence.
I can tell that I am a layman, you are trying to reference a table column that does not exist.
So, to solve this problem, first run the entire parent table create query, not the child table.
source to share