Mysql: Error 1215 (HY000): Unable to add foreign key constraints

FOREIGN KEY (index_col_name)
 REFERENCES table_name ( index_col_name,...)

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)
 );

I have read about database system concepts , 6th edition, Silberschatz. I will implement the university database system shown in Chapter 2 on MySQL’s OS X. But I have trouble creating a 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 a table course will cause the following error.

< p>

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’s foreign key constraint, I just learned that the word “foreign key constraint” means that the data of the foreign key column in the table course must exist in the primary key column of the table department Medium. But when inserting data, I I should have encountered this error.

If not, why did the author let me execute that SQL statement?

If I really execute the wrong SQL statement, after inserting some data, do I have to specify dept_name as a foreign key in the curriculum?

Edit: enter set foreign_key_checks = 0 to enter mysql>Do not fix errors.

--------------- ---------
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

FOREIGN The grammatical structure of KEY for CREATE TABLE is as follows:

FOREIGN KEY (index_col_name)
 REFERENCES table_name (index_col_name,...)

So your MySQL DDL should be:

create table course (
 cours e_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)
 );

In addition, dept_name in the department table should be VARCHAR(20)

More More information can be found in the MySQL documentation