title: MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint
body: <p>I have read <strong>Database system concepts</strong>, 6th edition, <em>Silberschatz</em>. 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 <code>course</code>. the table <code>department</code> looks like</p>
  
  <pre><code>mysql&gt; select * from department
      -&gt; ;
  +------------+----------+-----------+
  | 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&gt; show columns from department
      -&gt; ;
  +-----------+---------------+------+-----+---------+-------+
  | Field     | Type          | Null | Key | Default | Extra |
  +-----------+---------------+------+-----+---------+-------+
  | dept_name | varchar(20)   | NO   | PRI |         |       |
  | building  | varchar(15)   | YES  |     | NULL    |       |
  | budget    | decimal(12,2) | YES  |     | NULL    |       |
  +-----------+---------------+------+-----+---------+-------+
  </code></pre>
  
  <p>Creating the table <code>course</code> causes the following error.</p>
  
  <pre><code>mysql&gt; create table course
      -&gt; (course_id varchar(7),
      -&gt; title varchar (50),
      -&gt; dept_name varchar(20),
      -&gt; credits numeric(2,0),
      -&gt; primary key(course_id),
      -&gt; foreign key (dept_name) references department);
  ERROR 1215 (HY000): Cannot add foreign key constraint
  </code></pre>
  
  <p>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 <code>course</code> must exist in primary key column in the table <code>department</code>. But I should have met this error when inserting data.   </p>
  
  <p>If not, why does author make me execute that SQL statement? </p>
  
  <p>If I really execute erroneous SQL statement, Does I have to designate <code>dept_name</code> in course table as foreign key after inserting some data?</p>
  
  <p><strong>EDIT</strong> : typing <code>set foreign_key_checks=0</code> into <code>mysql&gt;</code> does not fix the error.</p>
  
  <pre><code>------------------------
  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&gt; set foreign_key_checks=0
      -&gt; ;
  Query OK, 0 rows affected (0.00 sec)
  mysql&gt; create table course
      -&gt; (course_id varchar(7),
      -&gt; title varchar(50),
      -&gt; dept_name varchar(20),
      -&gt; credits numeric(2,0),
      -&gt; primary key(course_id),
      -&gt; foreign key (dept_name) references department);
  ERROR 1215 (HY000): Cannot add foreign key constraint
  </code></pre>