MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint
When you get this vague error message, you can find out the more specific error by running
SHOW ENGINE INNODB STATUS;
The most common reasons are that when creating a foreign key, both the referenced field and the foreign key field need to match:
- Engine should be the same e.g. InnoDB
- Datatype should be the same, and with same length.
e.g. VARCHAR(20) or INT(10) UNSIGNED - Collation should be the same. e.g. utf8
- Unique - Foreign key should refer to field that is unique (usually private) in the reference table.
Another cause of this error is:
You have defined a SET NULL condition though some of the columns are defined as NOT NULL.
The syntax of FOREIGN KEY
for CREATE TABLE
is structured as follows:
FOREIGN KEY (index_col_name)
REFERENCES table_name (index_col_name,...)
So your MySQL DDL should be:
create table course (
course_id varchar(7),
title varchar(50),
dept_name varchar(20),
credits numeric(2 , 0 ),
primary key (course_id),
FOREIGN KEY (dept_name)
REFERENCES department (dept_name)
);
Also, in the department
table dept_name
should be VARCHAR(20)
More information can be found in the MySQL documentation