MySQL Error 1005 (HY000) – Create Table Shows Errno 150

foreign keyMySQL

i tried to create tables as per given in my textbook in MySQL server as follows:

create table department
(dept_name      varchar(20), 
 building       varchar(15), 
 budget             numeric(12,2),
 primary key (dept_name)
);

create table course
(course_id      varchar(8), 
 title          varchar(50), 
 dept_name      varchar(20),
 credits        numeric(2,0),
 primary key (course_id),
 foreign key (dept_name) references department
);

the department table gets created, but an error 

ERROR 1005 (HY000): Can't create table 'test.course' (errno: 150)

is displayed.

The queries seem very simple. And i am unable to find any mistake.
What can be the possible error that is preventing me to create the table?

Best Answer

You are missing column name of parent table to which your child refer, while creating child table, do like below

create table department
(dept_name      varchar(20), 
 building       varchar(15), 
 budget             numeric(12,2),
 primary key (dept_name)
);

create table course
(course_id      varchar(8), 
 title          varchar(50), 
 dept_name      varchar(20),
 credits        numeric(2,0),
 primary key (course_id),
 foreign key (dept_name) references department(dept_name)
);
Related Question