MySQL FOREIGN KEY error, ON DELETE CASCADE

问题:

I have absolutely no clue why MySQL is having an issue with the second CREATE TABLE statement.

CREATE TABLE User(
    uid INTEGER, 
    url CHAR(100),
    firstname CHAR(40),
    lastname CHAR(40),
    PRIMARY KEY(uid)
);

The below is the one that causes problems:

CREATE TABLE Follows(
    uid INTEGER,
    url CHAR(100),
    PRIMARY KEY(uid,url),
    FOREIGN KEY(uid) REFERENCES User(uid), ON DELETE CASCADE,
    FOREIGN KEY(url) REFERENCES User(url), ON DELETE CASCADE
    );

Error I get is:

#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 'ON DELETE CASCADE, FOREIGN KEY(url) REFERENCES User(url), ON DELETE CASCADE)' at line 1

回答1:

There are a few issues here:

First the on delete cascade is part of the foreign key definition, so the comma (,) before it should be removed.

Second, the second foreign key references url, which is not a unique key, and therefore is not allowed. So either remove this constraints:

CREATE TABLE Follows (
    uid INTEGER,  
    url CHAR(100),  
    PRIMARY KEY(uid,url),  
    FOREIGN KEY(uid) REFERENCES User(uid) ON DELETE CASCADE
);

Or define another unique key on url:

CREATE TABLE User(
    uid INTEGER, 
    url CHAR(100),
    firstname CHAR(40),
    lastname CHAR(40),
    PRIMARY KEY(uid),
    UNIQUE (url)
);


CREATE TABLE Follows (
    uid INTEGER,  
    url CHAR(100),  
    PRIMARY KEY(uid,url),  
    FOREIGN KEY(uid) REFERENCES User(uid) ON DELETE CASCADE,
    FOREIGN KEY(url) REFERENCES User(url) ON DELETE CASCADE
);

回答2:

try to remove after REFERENCES User(uid) and REFERENCES User(url) this ","

回答3:

you have to put a gap after User(uid) ON DELETE CASCADE, also you should write in to the end of the query like this: engine='innodb';

i mean between the last ) and ;