CODEWITHSUNDEEP
mysqlsqldatabaseforeign-keys
Damon
Damon

Reputation: 119

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

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

Upvotes: 7

Views: 16484

Answers (2)

jmail
jmail

Reputation: 6134

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

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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

Upvotes: 6

Related Questions