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