CODEWITHSUNDEEP
mysqlsqlmysql-error-1064
user1086348
user1086348

Reputation: 81

MySQL error #1064

Hi all I can't find the error in this table creation bit, seems really straight forward to be, here's what it's giving me:

ERROR 1064 at line 3: 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 'FOREIGN KEY(courses_courseDepartmentAbbv))' at line 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)
);

Upvotes: 2

Views: 2443

Answers (3)

Justin Pihony
Justin Pihony

Reputation: 67135

You need to have a References after each Foreign key. You are missing that in the beginning setup of courses. Here is the documentation

I think this is more of what you want. Your order of creation was not correct. You had foreign keys in the wrong location due to this. You only set up foreign key mappings on the tables with the relations to the PK. You only need to set up the PK on your other tables. As long as you create the tables in the right order, then you can do this, as below.

So, teachers and departments have primary keys that are foreign keys within the courses table. teachers and departments does not need to worry about the foreign key. You leave that up to the table that actually has the reference (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)
);

Upvotes: 2

user319198
user319198

Reputation:

Define engine type that should be innodb which supports FOREIGN KEY constraints.

Follow Syntax defined here

Upvotes: 0

Chetter Hummin
Chetter Hummin

Reputation: 6837

You need to give a REFERENCES clause for your FOREIGN KEY clauses

FOREIGN KEY (courses_courseTeacher)
FOREIGN KEY (courses_courseDepartmentAbbv)

Upvotes: 0

Related Questions