我正在尝试创建数据库,我已经编写了代码,将其导入phpMyAdmin时,出现以下错误:

#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


以及我编写的代码:

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


我在这里检查了其他错误#1064问题,但似乎没有帮助。

关于我为什么会收到此错误的任何想法?

最佳答案

您的脚本中存在循环依赖项:QUESTION引用OPTION,反之亦然。

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

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


要么删除它们(循环依赖通常是设计缺陷),要么稍后在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);


另外,OPTION是保留字,您最好将其括在Backtips中或选择其他名称。

关于mysql - 来自创建表的MySQL错误#1064,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/17007163/

10-13 02:47
查看更多