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.
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:
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;