Nishon Tandukar Nishon Tandukar - 4 months ago 14
SQL Question

Cannot seem to be able to debug MySQL error “#1064

I am sure this question has been asked far to many times.
If anyone could give it a look would be be great


I get #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 'REFERENCES interests(interests_id))' while creating category_interests and event_attendees table using interests.interest_id as reference to define foreign keys.


I cannot seem to find out a reason for it.

SET foreign_key_checks = 0;

DROP table IF EXISTS categories;
CREATE table categories(
category_id int AUTO_INCREMENT,
category_name VARCHAR(40),
category_desc TEXT,
CONSTRAINT pk_categories PRIMARY KEY (category_id)
);

DROP table IF EXISTS interests;
CREATE table interests(
interest_id int AUTO_INCREMENT,
interest_name varchar(40),

CONSTRAINT pk_interests PRIMARY KEY (interest_id)
);


-- ERROR - query does not run
DROP table IF EXISTS category_interests;
CREATE table category_interests(
interest_id int,
category_id int,
CONSTRAINT fk_interests FOREIGN KEY interest_id REFERENCES interests(interest_id),
CONSTRAINT fk_categories FOREIGN KEY category_id REFERENCES categories(category_id)
);

-- ERROR - query does not run
DROP table IF EXISTS event_atendees;
CREATE table event_atendees(
attendee_id int AUTO_INCREMENT,
interest_id int,
lat double,
lon double,
name varchar(115),
dob date,
gender CHAR(1),

CONSTRAINT pk_event_atendees PRIMARY KEY (attendee_id),
CONSTRAINT fk_intrests_ea FOREIGN KEY interests_id REFERENCES `interests`(interests_id)
);

SET foreign_key_checks = 1;

Answer

The faults I've found:

  1. You forgot to enclose the foreign key column by (). It should be CONSTRAINT fk_interests FOREIGN KEY (interest_id) REFERENCES interests(interest_id). It should not be CONSTRAINT fk_interests FOREIGN KEY interest_id.... (without parentheses)
  2. Somewhere you used interests_id. But you should have used interest_id.

Try this:

SET foreign_key_checks = 0;

DROP table IF EXISTS categories;
CREATE table categories(
  category_id int AUTO_INCREMENT,
  category_name VARCHAR(40),
  category_desc TEXT,
  CONSTRAINT pk_categories PRIMARY KEY (category_id)
);

DROP table IF EXISTS interests;
CREATE table interests(
  interest_id int AUTO_INCREMENT,
  interest_name varchar(40),

  CONSTRAINT pk_interests PRIMARY KEY (interest_id)
);


-- ERROR - query does not run 
DROP table IF EXISTS category_interests;
CREATE table category_interests(
  interest_id int,
  category_id int,
  CONSTRAINT fk_interests FOREIGN KEY (interest_id) REFERENCES interests(interest_id),
  CONSTRAINT fk_categories FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

-- ERROR - query does not run
DROP table IF EXISTS event_atendees;
CREATE table event_atendees(
     attendee_id int AUTO_INCREMENT,
    interest_id int,
    lat double,
    lon double,
    name varchar(115),
    dob date,
    gender CHAR(1),

  CONSTRAINT pk_event_atendees PRIMARY KEY (attendee_id),
  CONSTRAINT fk_intrests_ea FOREIGN KEY (interest_id) REFERENCES `interests`(interest_id)
);

SET foreign_key_checks = 1;

WORKING DEMO