MySQL错误＃1064

Question

大家好我无法找到在此表中创建位错误，似乎真的直截了当地是，这里就是它给我：

错误1064第3行：您有一个您的SQL语法错误;检查对应于你的MySQL服务器版本的权利 语法使用近“的外键（courses_courseDepartmentAbbv））”在 线 手册8

DROP TABLE IF EXISTS courses; 

CREATE TABLE courses(
    courses_courseNumber INT NOT NULL AUTO_INCREMENT, 
    courses_courseTitle VARCHAR(25) NOT NULL, 
    courses_courseTeacher VARCHAR(30) NOT NULL, 
    courses_courseCostOfBooks DECIMAL(5,2) NOT NULL, 
    courses_courseDepartmentAbbv CHAR(4) NOT NULL, 
    PRIMARY KEY (courses_courseNumber), 
    FOREIGN KEY (courses_courseTeacher), 
    FOREIGN KEY (courses_courseDepartmentAbbv) 
); 

DROP TABLE IF EXISTS departments; 

CREATE TABLE departments(
    departments_departmentAbbv CHAR(4) NOT NULL, 
    departments_departmentFullName VARCHAR(15) NOT NULL, 
    PRIMARY KEY (departments_departmentAbbv), 
    FOREIGN KEY (departments_departmentAbbv) REFERENCES (courses_courseDepartmentAbbv) 
); 

DROP TABLE IF EXISTS teachers; 

CREATE TABLE teachers(
      teachers_teacherName VARCHAR(20) NOT NULL, 
    teachers_teacherHomeroom SMALLINT(3) NOT NULL, 
    teachers_teacherHomeroomGrade SMALLINT(1) NOT NULL, 
    teachers_teacherFullTime BOOL NOT NULL, 
    PRIMARY KEY (teachers_teacherName), 
    FOREIGN KEY (teachers_teacherName) REFERENCES (courses_courseTeacher) 
); 

Answer 1

你需要在每个外键后参考文献。在开始设置课程时你错过了。这里是documentation

我认为这是更多你想要的。您的创作顺序不正确。由于这个，你的外键位于错误的位置。您只能在与PK关系的表上设置外键映射。你只需要在其他桌子上设置PK。只要你按照正确的顺序创建表格，那么你可以做到这一点，如下所示。

因此，teachers和departments的主键是courses表中的外键。 teachers和departments不需要担心外键。你离开，多达实际具有参考（courses）

DROP TABLE IF EXISTS teachers; 

CREATE TABLE teachers(
      teachers_teacherName VARCHAR(20) NOT NULL, 
    teachers_teacherHomeroom SMALLINT(3) NOT NULL, 
    teachers_teacherHomeroomGrade SMALLINT(1) NOT NULL, 
    teachers_teacherFullTime BOOL NOT NULL, 
    PRIMARY KEY (teachers_teacherName) 
    --FOREIGN KEY (teachers_teacherName) REFERENCES courses (courses_courseTeacher) 
    --This is not where you set up the FK for courses 
); 

DROP TABLE IF EXISTS departments; 

CREATE TABLE departments(
    departments_departmentAbbv CHAR(4) NOT NULL, 
    departments_departmentFullName VARCHAR(15) NOT NULL, 
    PRIMARY KEY (departments_departmentAbbv) 
    --FOREIGN KEY (departments_departmentAbbv) REFERENCES courses (courses_courseDepartmentAbbv) 
    --This is not where you set up the FK for courses 
); 

CREATE TABLE courses(
    courses_courseNumber INT NOT NULL AUTO_INCREMENT, 
    courses_courseTitle VARCHAR(25) NOT NULL, 
    courses_courseTeacher VARCHAR(30) NOT NULL, 
    courses_courseCostOfBooks DECIMAL(5,2) NOT NULL, 
    courses_courseDepartmentAbbv CHAR(4) NOT NULL, 
    PRIMARY KEY (courses_courseNumber), 
    FOREIGN KEY (courses_courseTeacher) 
     REFERENCES teachers (teachers_teacherName) 
    FOREIGN KEY (courses_courseDepartmentAbbv) 
     REFERENCES departments(departments_departmentAbbv) 
); 

Answer 2

定义应innodb支持FOREIGN KEY限制发动机类型的表。

Follow Syntax defined here

Answer 3

你需要给一个参考条款为您foreign key子句

FOREIGN KEY (courses_courseTeacher) 
FOREIGN KEY (courses_courseDepartmentAbbv)