foreign key reference syntax error

Question

I am having trouble creating members table with the following code. check the manual that corresponds to your MySQL server version for the right syntax to use near 'schoolID int NOT NULL FOREIGN KEY REFERENCES schools(schoolID),' at line 1

What is wrong with the syntax?

Thanks!

CREATE TABLE schools (
    schoolID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    parentID int NOT NULL DEFAULT 0,
    schoolname VARCHAR(199) NOT NULL,  
    active int NOT NULL,
    dateENTERED datetime NOT NULL
);
CREATE TABLE members
(
    memberID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    schoolID int NOT NULL FOREIGN KEY REFERENCES schools(schoolID),
    active int NOT NULL,
    dateENTERED datetime NOT NULL
);

score 3 · Accepted Answer · edited May 23 '17 at 12:11

CREATE TABLE members
(
    memberID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    schoolID int NOT NULL ,
    active int NOT NULL,
    dateENTERED datetime NOT NULL,
    CONSTRAINT member_FK FOREIGN KEY (schoolID) REFERENCES schools(schoolID)
);

you can also do it this way,

CREATE TABLE members
(
    memberID int NOT NULL AUTO_INCREMENT,
    schoolID int NOT NULL ,
    active int NOT NULL,
    dateENTERED datetime NOT NULL,
    CONSTRAINT member_PK PRIMARY KEY (memberID),
    CONSTRAINT member_FK FOREIGN KEY (schoolID) REFERENCES schools(schoolID)
);

SQLFiddle Demo

You may want to add a note about the in-line constraint definition bug — Bohemian, May 27 '13 at 05:47

foreign key reference syntax error

1 Answers1