MySQL错误:无法添加外键约束? [英] MySQL error: Cannot add foreign key constraint?

查看:284
本文介绍了MySQL错误:无法添加外键约束?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在我创建我的上一个表时,始终存在错误:无法添加外键约束



系统:Mac OS X 10.9

DB:MySQL 5.6.14

DBM:Sequel Pro

There always be the error:"Cannot add foreign key constraint" when I create my last table.


System: Mac OS X 10.9
DB : MySQL 5.6.14
DBM : Sequel Pro

CREATE TABLE users (
uid INT AUTO_INCREMENT PRIMARY KEY,
uname VARCHAR(20) NOT NULL,
uemail VARCHAR(20) NOT NULL,
ucity VARCHAR(20),
upassw VARCHAR(20) NOT NULL
);

CREATE TABLE songs(
sid INT AUTO_INCREMENT PRIMARY KEY,
sname VARCHAR(20) NOT NULL,
srldate DATE NOT NULL
);



CREATE TABLE albums (
albid INT AUTO_INCREMENT PRIMARY KEY,
albname VARCHAR(20) NOT NULL,
albrldate DATE NOT NULL,
albrltime TIME NOT NULL
);

CREATE TABLE artists (
aid INT AUTO_INCREMENT PRIMARY KEY,
aname VARCHAR(20) NOT NULL
);

CREATE TABLE genres (
gid INT AUTO_INCREMENT PRIMARY KEY,
gname VARCHAR(20) NOT NULL
);

CREATE TABLE playlists (
uid INT NOT NULL,
sid INT NOT NULL,
plname VARCHAR(20) NOT NULL,
plmdate DATE NOT NULL,
plmtime TIME NOT NULL,
PRIMARY KEY(uid, sid, plname, plmdate, plmtime),
FOREIGN KEY(uid) REFERENCES users(uid),
FOREIGN KEY(sid) REFERENCES songs(sid)
);

CREATE TABLE u_like_a (
aid INT NOT NULL,
uid INT NOT NULL,
PRIMARY KEY(aid, uid),
FOREIGN KEY(aid) REFERENCES artists(aid),
FOREIGN KEY(uid) REFERENCES users(uid)
);

CREATE TABLE be_fan (
aid INT NOT NULL,
uid INT NOT NULL,
PRIMARY KEY(aid, uid),
FOREIGN KEY(aid) REFERENCES artists(aid),
FOREIGN KEY(uid) REFERENCES users(uid)
);

CREATE TABLE follow (
uid INT NOT NULL,
to_uid INT NOT NULL,
PRIMARY KEY(uid, to_uid),
FOREIGN KEY(uid) REFERENCES users(uid),
FOREIGN KEY(to_uid) REFERENCES users(uid)
);

CREATE TABLE u_like_g (
gid INT NOT NULL,
uid INT NOT NULL,
PRIMARY KEY(gid, uid),
FOREIGN KEY(gid) REFERENCES genres(gid),
FOREIGN KEY(uid) REFERENCES users(uid)
);

CREATE TABLE u_share_pl(
uid INT NOT NULL,
from_uid INT NOT NULL,
plname VARCHAR(20) NOT NULL,
plmdate DATE NOT NULL,
plmtime TIME NOT NULL,
PRIMARY KEY(uid, from_uid, plname, plmdate, plmtime),
FOREIGN KEY(uid) REFERENCES users(uid),
FOREIGN KEY(from_uid) REFERENCES users(uid),
FOREIGN KEY(plname) REFERENCES playlists(plname),
FOREIGN KEY(plmdate) REFERENCES playlists(plmdate),
FOREIGN KEY(plmtime) REFERENCES playlists(plmtime)
); #####--->  This is the last table. 

错误来自这里。我真的不为什么。
我检查了所有属性的类型。属性的类型和名称没有问题。
但是mysql总是说无法添加外键约束

ERROR comes from here. I really don't why. I have check the type for all attributes. The type and name of attributes have no problem. But the mysql always say "Cannot add foreign key constraint"

推荐答案

您在上表中引用错误的forgien REFERENCES位用户(from_uid)

Here is you reference wrong forgien REFERENCES users(from_uid) in last table.

FOREIGN KEY(from_uid) REFERENCES users(from_uid)

from_uid 不属于用户

这应该是

FOREIGN KEY(from_uid) REFERENCES users(uid)

您的 playLists表有四列的主键组合,因此您应该在 u_share_pl表中提供所有这四列作为forieng键

your playLists table has primary key combination of four columns, so you should supply all these four columns as forieng key in u_share_pl table.

另一个作为参考的复合键应该是单个约束,例如

Another composite key as a reference should be a single constraint like

FOREIGN KEY(from_uid,sid,plname,plmdate,plmtime) REFERENCES playlists(uid,sid,plname,plmdate,plmtime)

您最后一张表格应该是:

Your last table Create should be:

CREATE TABLE u_share_pl(
uid INT NOT NULL,
from_uid INT NOT NULL,
sid INT NOT NULL,
plname VARCHAR(20) NOT NULL,
plmdate DATE NOT NULL,
plmtime TIME NOT NULL,
PRIMARY KEY(uid, from_uid, plname, plmdate, plmtime),
FOREIGN KEY(uid) REFERENCES users(uid),
FOREIGN KEY(from_uid,sid,plname,plmdate,plmtime) REFERENCES playlists(uid,sid,plname,plmdate,plmtime)
);

这篇关于MySQL错误:无法添加外键约束?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆