1215 Cannot add foreign key constraint

Question

i try to create table workouts with two foreign key fields. But MySQL shows me error [1215] Cannot add foreign key constraint. I`ve checked that parent tables are created and both fields have the same type. What else can be wrong?

drop table if exists areas;
drop table if exists roles;
drop table if exists trainers;
drop table if exists users;
drop table if exists workouts;
drop table if exists user_roles;
drop table if exists trainers_areas;

CREATE TABLE areas(
  id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  name VARCHAR(30)
);

CREATE TABLE roles(
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  role VARCHAR(30)
);

create TABLE trainers(
  id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  name VARCHAR(30) NOT NULL,
  birthday TIMESTAMP,
  sex CHAR(1)
);

create TABLE users(
  id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
  name VARCHAR(30) NOT NULL,
  email VARCHAR(20),
  password VARCHAR(20),
  registered TIMESTAMP DEFAULT now(),
  enabled BOOL DEFAULT TRUE
);

CREATE TABLE workouts(
  id INTEGER UNSIGNED PRIMARY KEY  NOT NULL AUTO_INCREMENT,
  area_id INTEGER UNSIGNED NOT NULL,
  trainer_id INTEGER UNSIGNED NOT NULL,
  date TIMESTAMP,
  completed BOOLEAN NOT NULL,
 CONSTRAINT FOREIGN KEY (area_id) REFERENCES areas(id),
 CONSTRAINT FOREIGN KEY (trainer_id) REFERENCES trainers(id)
);


CREATE TABLE users_roles(
  user_id INTEGER UNSIGNED NOT NULL,
  role_id INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (role_id) REFERENCES roles(id)
);

CREATE TABLE trainers_areas(
  area_id INTEGER UNSIGNED NOT NULL,
  treiner_id INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (area_id) REFERENCES areas(id),
  FOREIGN KEY (treiner_id) REFERENCES trainers(id),
  UNIQUE (treiner_id, area_id)
);

Answer 1

added ENGINE=INNODB after each block.

Answer 2

datatypes must match, and SIGN too. If you don't specify UNSIGNED, then it is signed.

Messing with foreign_key_checks as mentioned in another answer ranks up there with the last thing I would do. People often forget they did it, and guess where the questions show up: Stackoverflow.

The below will work:

Create testbed:

create schema Monday001a;
use Monday001a;

Script:

drop table if exists areas;
drop table if exists roles;
drop table if exists trainers;
drop table if exists users;
drop table if exists workouts;
drop table if exists user_roles;
drop table if exists trainers_areas;

CREATE TABLE areas(
  id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  name VARCHAR(30)
);

CREATE TABLE roles(
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  role VARCHAR(30)
);

create TABLE trainers(
  id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  name VARCHAR(30) NOT NULL,
  birthday TIMESTAMP,
  sex CHAR(1)
);

create TABLE users(
  id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  name VARCHAR(30) NOT NULL,
  email VARCHAR(20),
  password VARCHAR(20),
  registered TIMESTAMP DEFAULT now(),
  enabled BOOL DEFAULT TRUE
);

CREATE TABLE workouts(
  id INTEGER UNSIGNED PRIMARY KEY  NOT NULL AUTO_INCREMENT,
  area_id INTEGER UNSIGNED NOT NULL,
  trainer_id INTEGER UNSIGNED NOT NULL,
  date TIMESTAMP,
  completed BOOLEAN NOT NULL,
 CONSTRAINT FOREIGN KEY (area_id) REFERENCES areas(id),
 CONSTRAINT FOREIGN KEY (trainer_id) REFERENCES trainers(id)
);


CREATE TABLE users_roles(
  user_id INTEGER UNSIGNED NOT NULL,
  role_id INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (role_id) REFERENCES roles(id)
);

CREATE TABLE trainers_areas(
  area_id INTEGER UNSIGNED NOT NULL,
  treiner_id INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (area_id) REFERENCES areas(id),
  FOREIGN KEY (treiner_id) REFERENCES trainers(id),
  UNIQUE (treiner_id, area_id)
);

Cleanup:

drop schema Monday001a;

From the MySQL Manual Page entitled Using FOREIGN KEY Constraints:

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

Answer 3

To find the specific error run this:

SHOW ENGINE INNODB STATUS

And look in the LATEST FOREIGN KEY ERROR section.

The data type for the child column must match the parent column exactly. For example, since medicalhistory.MedicalHistoryID is an INT, Patient.MedicalHistory also needs to be an INT, not a SMALLINT.

Also, you should run the query set foreign_key_checks=0 before running the DDL so you can create the tables in an arbitrary order rather than needing to create all parent tables before the relevant child tables.