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

Question

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 ?

Ravinder Reddy · Accepted Answer · 2014-01-22 08:31:28Z

1

You have not included field from parent table to refer.

It should be:

FOREIGN KEY ( dept_name ) REFERENCES department( dept_name )

answered Jan 22, 2014 at 8:31

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zzlalani · Accepted Answer · 2014-01-22 08:31:38Z

0

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

answered Jan 22, 2014 at 8:31

zzlalani

24.6k16 gold badges48 silver badges73 bronze badges

Comments

domdomcodecode · Accepted Answer · 2014-01-22 08:34:30Z

0

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

answered Jan 22, 2014 at 8:34

domdomcodecode

2,4734 gold badges21 silver badges27 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related