MySQL Error #1064 from Create Table

Question

I'm trying to create a database, I've written the code, when I import it to phpMyAdmin, I get the following error:

#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 'OPTION(OPTION_ID)
)' at line 7

and the code I've written:

CREATE DATABASE MAWARID;
USE MAWARID;

CREATE TABLE TOPIC (
    TOPIC_ID INT NOT NULL AUTO_INCREMENT,
    TOPIC_NAME VARCHAR(30) NOT NULL,

    PRIMARY KEY (TOPIC_ID)
);

CREATE TABLE QUESTION (
    QUESTION_ID INT NOT NULL AUTO_INCREMENT,
    QUESTION_NAME VARCHAR(100) NOT NULL,
    ANSWER INT NOT NULL,

    PRIMARY KEY (QUESTION_ID),
    FOREIGN KEY (ANSWER) REFERENCES OPTION(OPTION_ID)
);

CREATE TABLE OPTION (
    OPTION_ID INT NOT NULL AUTO_INCREMENT,
    OPTION_NAME VARCHAR(100) NOT NULL,
    QUESTION INT NOT NULL,

    PRIMARY KEY (OPTION_ID),
    FOREIGN KEY (QUESTION) REFERENCES QUESTION(QUESTION_ID)
);

I've checked other error #1064 question here, but non seem to help me.

Any thoughts on why I'm getting this error?

Answer 1

There are circular dependencies in your script : QUESTION references OPTION and vice-versa.

CREATE TABLE QUESTION (
    [...]
    FOREIGN KEY (ANSWER) REFERENCES OPTION(OPTION_ID)
);

CREATE TABLE OPTION (
    [...]
    FOREIGN KEY (QUESTION) REFERENCES QUESTION(QUESTION_ID)
);

Either you remove them (circular dependencies are often design flaws) or you try adding the foreign key later in QUESTION :

CREATE DATABASE MAWARID;
USE MAWARID;

CREATE TABLE TOPIC (
    TOPIC_ID INT NOT NULL AUTO_INCREMENT,
    TOPIC_NAME VARCHAR(30) NOT NULL,
    PRIMARY KEY (TOPIC_ID)
);

CREATE TABLE QUESTION (
    QUESTION_ID INT NOT NULL AUTO_INCREMENT,
    QUESTION_NAME VARCHAR(100) NOT NULL,
    ANSWER INT NOT NULL,
    PRIMARY KEY (QUESTION_ID)
    -- no foreign key creation here
);

CREATE TABLE `OPTION` (
    OPTION_ID INT NOT NULL AUTO_INCREMENT,
    OPTION_NAME VARCHAR(100) NOT NULL,
    QUESTION INT NOT NULL,

    PRIMARY KEY (OPTION_ID),
    FOREIGN KEY (QUESTION) REFERENCES QUESTION(QUESTION_ID)
);

-- create your foreign key here
ALTER TABLE QUESTION ADD FOREIGN KEY (ANSWER) REFERENCES `OPTION`(OPTION_ID);

Also, OPTION is a reserved word, you'd better enclose it into backtips or choose another name.