Error code 1215: cannot add foreign key constraint MySQL

Viewed 382

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;
2 Answers
Related