MySQL FOREIGN KEY错误,已删除级联 [英] MySQL FOREIGN KEY error, ON DELETE CASCADE
问题描述
我绝对不知道MySQL为什么第二条 CREATE TABLE
语句有问题。
CREATE TABLE User(
uid INTEGER,
url CHAR(100),
firstname CHAR(40),
lastname CHAR(40),
主键(uid)
);
以下是导致问题的原因:
CREATE TABLE Follows(
uid INTEGER,
url CHAR(100),
PRIMARY KEY(uid,url),
FOREIGN KEY(uid)参考用户(uid),已删除级联,
FOREIGN KEY(url)参考用户(URL),已删除级联
);
我得到的错误是:
#1064-您的SQL语法有错误;在第1行
<,请检查与您的MySQL服务器版本相对应的手册,以获取在'ON DELETE CASCADE,FOREIGN KEY(url)参考用户(URL),ON DELETE CASCADE)附近使用的正确语法。 / blockquote>
解决方案这里有一些问题:
首先是
删除级联
是外键定义的一部分,因此逗号(,
)之前应将其删除。
第二,第二个外键引用
url
,它不是唯一键,因此是不允许的。因此,要么删除此约束,要么:CREATE TABLE Follows(
uid INTEGER,
url CHAR(100) ,
主键(uid,url),
外部键(uid)引用用户(uid)删除级联
);
或在
url
上定义另一个唯一键:创建表User(
uid INTEGER,
url CHAR(100),
firstname CHAR(40),
姓氏CHAR(40),
主键(uid),
UNIQUE(url)
);
创建表如下(
uid INTEGER,
url CHAR(100),
PRIMARY KEY(uid,url),
FOREIGN KEY(uid)参考删除级联上的用户(uid),
FOREIGN KEY(url)参考删除级联上的用户(url)
);
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 );
这篇关于MySQL FOREIGN KEY错误,已删除级联的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!