mysql 删除foreign key,MySQL FOREIGN KEY错误，已删除级联

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

解决方案

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

);