MySQL Database Error #1064 Syntax Error

Question

Help! I keep getting an 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 'MGRDATE DATE, PRIMARY KEY (DNUM), FOREIGN KEY (MGRSSN) REFERENCES EMPL' at line 6

CREATE TABLE EMPLOYEE ( 
    SSN INT NOT NULL, 
    FNAME VARCHAR(15) NOT NULL, 
    MINIT VARCHAR(1) NOT NULL, 
    LNAME VARCHAR(15) NOT NULL, 
    BDATE DATE, 
    ADDRESS VARCHAR(50), 
    SEX VARCHAR(1), 
    SALARY INT NOT NULL, 
    SUPERSSN INT NOT NULL, 
    DNO INT NOT NULL, 
    PRIMARY KEY (SSN)
);

CREATE TABLE DEPARTMENT ( 
    DNUM INT NOT NULL, 
    DNAME VARCHAR(15) NOT NULL, 
    MGRSSN INT NOT NULL,
    INDEX (MSGSSN)
    MGRDATE DATE, 
    PRIMARY KEY (DNUM), 
    FOREIGN KEY (MGRSSN) 
        REFERENCES EMPLOYEE(SSN)
        ON DELETE CASCADE,
); 

CREATE TABLE WORKS_ON ( 
    SSN INT NOT NULL, 
    INDEX (SSN)
    PNO INT NOT NULL,
    INDEX (PNO) 
    HOURS INT NOT NULL, 
    FOREIGN KEY (SSN) 
        REFERENCES EMPLOYEE(SSN)
        ON DELETE CASCADE,
    FOREIGN KEY (PNO) 
        REFERENCES PROJECT(PINT)
        ON DELETE CASCADE,
); 

CREATE TABLE DEPT_LOCATION ( 
    DNUM INT NOT NULL, 
    DLOCATION VARCHAR(20) NOT NULL
);

CREATE TABLE DEPENDENT ( 
    SSN INT NOT NULL, 
    INDEX (SSN)
    DEPENDENT_NAME VARCHAR(10) NOT NULL, 
    SEX VARCHAR(1) NOT NULL, 
    DOB DATE, 
    RELATIONSHIP VARCHAR(10) NOT NULL,
    FOREIGN KEY (SSN) 
        REFERENCES EMPLOYEE(SSN)
        ON DELETE CASCADE,
);

CREATE TABLE PROJECT (
    PNAME VARCHAR(15) NOT NULL, 
    PINT INT NOT NULL, 
    PLOCATION VARCHAR(20) NOT NULL,
    INDEX (PLOCATION) 
    DNUM INT NOT NULL, 
    INDEX (DNUM)
    PRIMARY KEY (PINT), 
    FOREIGN KEY (PLOCATION) 
        REFERENCES DEPT_LOCATION(DLOCATION)
        ON DELETE CASCADE,
    FOREIGN KEY (DNUM) 
        REFERENCES “DEPARTMENT” (DNUM)
        ON DELETE CASCADE,
);

jmail · Accepted Answer · 2014-04-09 05:59:35Z

1

your using the , lot place, you should must remove the comma in query.

following table you should change:

1.DEPARTMENT 
2.WORKS_ON 
3.DEPENDENT 
4.PROJECT

your query:

 ON DELETE CASCADE,  -- -->remove the comma
);

change to:

 ON DELETE CASCADE
);

you should doing spelling mistake in you department table like as following to as:

your query:

    MGRSSN INT NOT NULL,
    INDEX (MSGSSN)

change to:

      MGRSSN INT NOT NULL,    
      INDEX (MGRSSN) ,

edited Apr 9, 2014 at 5:59

answered Apr 9, 2014 at 5:46

jmail

6,1503 gold badges23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sushant · Accepted Answer · 2014-04-09 06:18:42Z

In your code you have missed comma(,) in some lines and also placed extra comma.

In DEPARTMENT table you give column name MGRSSN and used as INDEX (MSGSSN) its wrong use INDEX (MGRSSN) instead of INDEX (MSGSSN).

In WORKS_ON table you place a foreign key with PROJECT table and you try to create WORKS_ON table before PROJECT table.

In PROJECT table you try to assign a foreign key to DEPT_LOCATION (DLOCATION) but there is no primary key assigned in DEPT_LOCATION to DLOCATION so there should be an error is occured.

Please try following code.

CREATE TABLE EMPLOYEE ( 
    SSN INT NOT NULL, 
    FNAME VARCHAR(15) NOT NULL, 
    MINIT VARCHAR(1) NOT NULL, 
    LNAME VARCHAR(15) NOT NULL, 
    BDATE DATE, 
    ADDRESS VARCHAR(50), 
    SEX VARCHAR(1), 
    SALARY INT NOT NULL, 
    SUPERSSN INT NOT NULL, 
    DNO INT NOT NULL, 
    PRIMARY KEY (SSN)
);

CREATE TABLE DEPARTMENT ( 
    DNUM INT NOT NULL, 
    DNAME VARCHAR(15) NOT NULL, 
    MGRSSN INT NOT NULL,
    INDEX (MGRSSN),
    MGRDATE DATE, 
    PRIMARY KEY (DNUM), 
    FOREIGN KEY (MGRSSN) 
        REFERENCES EMPLOYEE(SSN)
        ON DELETE CASCADE
);

CREATE TABLE DEPT_LOCATION ( 
    DNUM INT NOT NULL, 
    DLOCATION VARCHAR(20) NOT NULL, 
    PRIMARY KEY (DLOCATION)
);

CREATE TABLE DEPENDENT ( 
    SSN INT NOT NULL, 
    INDEX (SSN),
    DEPENDENT_NAME VARCHAR(10) NOT NULL, 
    SEX VARCHAR(1) NOT NULL, 
    DOB DATE, 
    RELATIONSHIP VARCHAR(10) NOT NULL,
    FOREIGN KEY (SSN) 
        REFERENCES EMPLOYEE(SSN)
        ON DELETE CASCADE
);

CREATE TABLE PROJECT (
    PNAME VARCHAR(15) NOT NULL, 
    PINT INT NOT NULL, 
    PLOCATION VARCHAR(20) NOT NULL,
    INDEX (PLOCATION),
    DNUM INT NOT NULL, 
    INDEX (DNUM),
    PRIMARY KEY (PINT), 
    FOREIGN KEY (PLOCATION) 
        REFERENCES DEPT_LOCATION(DLOCATION)
        ON DELETE CASCADE,
    FOREIGN KEY (DNUM) 
        REFERENCES DEPARTMENT (DNUM)
        ON DELETE CASCADE
);  

CREATE TABLE WORKS_ON ( 
    SSN INT NOT NULL, 
    INDEX (SSN),
    PNO INT NOT NULL,
    INDEX (PNO), 
    HOURS INT NOT NULL, 
    FOREIGN KEY (SSN) 
        REFERENCES EMPLOYEE(SSN)
        ON DELETE CASCADE,
    FOREIGN KEY (PNO) 
        REFERENCES PROJECT(PINT)
        ON DELETE CASCADE
);

It works.

Collectives™ on Stack Overflow

MySQL Database Error #1064 Syntax Error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related