MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint

Question

I have read Database system concepts, 6th edition, Silberschatz. I'm going to implement the university database system shown in chapter 2 on OS X on MySQL. But I have a trouble with creating the table course. the table department looks like

mysql> select * from department     -> ; +------------+----------+-----------+ | dept_name  | building | budget    | +------------+----------+-----------+ | Biology    | Watson   |  90000.00 | | Comp. Sci. | Taylor   | 100000.00 | | Elec. Eng. | Taylor   |  85000.00 | | Finance    | Painter  | 120000.00 | | History    | Painter  |  50000.00 | | Music      | Packard  |  80000.00 | | Physics    | Watson   |  70000.00 | +------------+----------+-----------+  mysql> show columns from department     -> ; +-----------+---------------+------+-----+---------+-------+ | Field     | Type          | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------+-------+ | dept_name | varchar(20)   | NO   | PRI |         |       | | building  | varchar(15)   | YES  |     | NULL    |       | | budget    | decimal(12,2) | YES  |     | NULL    |       | +-----------+---------------+------+-----+---------+-------+ 

Creating the table course causes the following error.

mysql> 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); ERROR 1215 (HY000): Cannot add foreign key constraint 

after searching google for foreign key constraint, I have just learned that the word 'foreign key constraint' indicates that data from foreign key column in the table course must exist in primary key column in the table department. But I should have met this error when inserting data.

If not, why does author make me execute that SQL statement?

If I really execute erroneous SQL statement, Does I have to designate dept_name in course table as foreign key after inserting some data?

EDIT : typing set foreign_key_checks=0 into mysql> does not fix the error.

------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2013-09-21 16:02:20 132cbe000 Error in foreign key constraint of table university/course: foreign key (dept_name) references department): Syntax error close to: ) mysql> set foreign_key_checks=0     -> ; Query OK, 0 rows affected (0.00 sec) mysql> 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); ERROR 1215 (HY000): Cannot add foreign key constraint 

Answer 1

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.

Answer 2

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