Trouble creating foreign key in MySQL

Question

I have two create table statements with the second table implementing a foreign key. For some reason I am getting a #1064 error.

CREATE TABLE position (
 id INT(3)  AUTO_INCREMENT PRIMARY KEY,

 job_description VARCHAR(100) NOT NULL,

)  

CREATE TABLE employee (    
    empNO INT(3)  AUTO_INCREMENT PRIMARY KEY,

    First_Name VARCHAR(50) NOT NULL,

    Last_Name VARCHAR(50) NOT NULL,

    isLoyal INT (5),

    PositionID INT(3) NOT NULL,

    Foreign Key (PositionID) REFERENCES position(id)       
    )

#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 'position(id) )' at line 12 — , Mar 24 '15 at 11:43

score 0 · Answer 1 · edited May 23 '17 at 11:45

0

You are missing closing semi-colons on each statement and there is a trailing comma after this line:

job_description VARCHAR(100) NOT NULL, which shouldn't be there.

This is probably the best resource for this error: How can I fix MySQL error #1064?

edited May 23 '17 at 11:45

Community

1
1

answered Mar 24 '15 at 11:43

Matt Kirwan

21
3

Semicolon makes no difference. When I change it to position.id the error changes to #1215 - Cannot add foreign key constraint . – Mar 24 '15 at 11:44
I had already corrected the trailing comma. That table created fine it's the second table I'm having trouble with. – Mar 24 '15 at 11:47
@chris211313 - It may be because the PositionID is NOT NULL and the corresponding `id` column in `position` isn't. – Matt Kirwan Mar 24 '15 at 11:51

score 0 · Accepted Answer · answered Mar 24 '15 at 12:20

POSTION is a predefined keyword in mysql, that's why you got error.

CREATE TABLE position1 (
 id INT(3)  AUTO_INCREMENT PRIMARY KEY,

 job_description VARCHAR(100) NOT NULL,

)   




CREATE TABLE employee (    
        empNO INT(3)  AUTO_INCREMENT PRIMARY KEY,

        First_Name VARCHAR(50) NOT NULL,

        Last_Name VARCHAR(50) NOT NULL,

        isLoyal INT (5),

        PositionID INT(3) NOT NULL,

        FOREIGN KEY (PositionID) REFERENCES position1(id)      
        )

Trouble creating foreign key in MySQL

2 Answers2