CODEWITHSUNDEEP
mysql
user3184496
user3184496

Reputation: 5

Getting error in sql execution for creating table with foreign key constraint

When I execute the following SQL query:

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

I get the following error:

MySQL said: Documentation

1215 - Cannot add foreign key constraint

Any insights to help fix this issue ?

Upvotes: 0

Views: 59

Answers (3)

domdomcodecode
domdomcodecode

Reputation: 2453

You need to specify which column in your referenced table to match. Your query should look like 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 ( dept_name )
);

Upvotes: 0

zzlalani
zzlalani

Reputation: 24394

The reference should be like REFERENCES TABLE( COLUMN_NAME )

FOREIGN KEY ( dept_name ) REFERENCES department( dept_name )

It will then look like

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

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 24022

You have not included field from parent table to refer.

It should be: 

FOREIGN KEY ( dept_name ) REFERENCES department( dept_name )

Upvotes: 1

Related Questions