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
NVM .. fixed it. had to add values for each referencing keys — Damon

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

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

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

2 Answers