Getting syntax error when declaring FOREIGN KEYS in MYSQL (using innoDB)

Question

I have the following tables:

CREATE TABLE IF NOT EXISTS books (

book_id varchar(50) NOT NULL,

book_title varchar(50) NOT NULL,

courseid varchar(50) NOT NULL,

FOREIGN KEY ('courseid') REFERENCES 'course'('course_id') ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS course (

course_id varchar(50) NOT NULL,

year varchar(50) NOT NULL,

section varchar(50) NOT NULL,

PRIMARY KEY (course_id)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want to make courseid(under books table) a foreign key referencing course_id (under course table) but getting the following error message:

**#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''courseid') REFERENCES 'course'('course_id')

) ENGINE=InnoDB DEFAULT CHARS' at line 15**

I tired several syntax for declaring Foreign key (using CONSTRAINT FOREIGN KEY, with/without quotes etc) but none worked.

I am using Xamp WITH Server version: 5.5.32 - MySQL Community Server (GPL).

Can someone please help me with this issue?

Thanks

Edit: Syntax error is fixed but now I am getting this error: "#1452 - Cannot add or update a child row: a foreign key constraint fails (testlib2.books, CONSTRAINT books_ibfk_1 FOREIGN KEY (courseid) REFERENCES course (course_id) ON DELETE CASCADE)" — Damon
– Damon, Commented Mar 6, 2014 at 1:29
NVM .. fixed it. had to add values for each referencing keys — Damon
– Damon, Commented Mar 6, 2014 at 3:45

M Khalid Junaid · Accepted Answer · 2014-03-05 23:16:28Z

6

Use back-ticks ` instead of single quotes also first create course table then create books table so that books table can able to point course table

CREATE TABLE IF NOT EXISTS course (
  course_id VARCHAR (50) NOT NULL,
  YEAR VARCHAR (50) NOT NULL,
  section VARCHAR (50) NOT NULL,
  PRIMARY KEY (course_id)
) ENGINE = INNODB DEFAULT CHARSET = latin1 ;

CREATE TABLE IF NOT EXISTS books (
  book_id VARCHAR (50) NOT NULL,
  book_title VARCHAR (50) NOT NULL,
  courseid VARCHAR (50) NOT NULL,
  FOREIGN KEY (`courseid`) REFERENCES `course` (`course_id`) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET = latin1 ;

See fiddle demo

answered Mar 5, 2014 at 23:16

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

1 Comment

Damon Over a year ago

I think my syntax problem is fixed but now I am getting the following error: "#1452 - Cannot add or update a child row: a foreign key constraint fails (testlib2.books, CONSTRAINT books_ibfk_1 FOREIGN KEY (courseid) REFERENCES course (course_id) ON DELETE CASCADE)"

jmail · Accepted Answer · 2014-03-06 05:17:01Z

1

make it simply as :

your code:

FOREIGN KEY ('courseid') REFERENCES 'course'('course_id') ON DELETE CASCADE

change:

FOREIGN KEY (`courseid`) REFERENCES `course` (`course_id`) ON DELETE CASCADE

And you must going to create the primary key table of course(course_id) and then after only should to be create the reference foreign key table of books(courseid).

answered Mar 6, 2014 at 5:17

jmail

6,1503 gold badges23 silver badges35 bronze badges

1 Comment

scarface Over a year ago

Why are the ` chars necessary?

Collectives™ on Stack Overflow

Getting syntax error when declaring FOREIGN KEYS in MYSQL (using innoDB)

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related