trouble creating table using foreign key

Question

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

This is table that i created before...

now i am writing this:

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

an it shows :

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

Possible duplicate of [this](https://stackoverflow.com/questions/9018584/error-code-1005-cant-create-table-errno-150) SO question. — Jeroen Heier, Jul 08 '17 at 07:31

score 1 · Accepted Answer · answered Jul 08 '17 at 07:29

1

You're missing a portion of the FOREIGN KEY requirement, as you cannot simply refer to another table:

foreign key (dept_name) references department(dept_name)

answered Jul 08 '17 at 07:29

Paul T.

4,703
11
25
29

score 1 · Answer 2 · answered Jul 08 '17 at 07:36

1

Add the reference table field name PFB

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

answered Jul 08 '17 at 07:36

Palani Samy

169
9

trouble creating table using foreign key

2 Answers2