使用两个外键作为主键 - MySQL [英] Using two foreign keys as a primary key - MySQL
问题描述
我是新的MySQL(不得不学习它uni)。
我必须为一个赋值创建一个数据库和web接口。
I am quite new to MySQL (have had to learn it for uni). I have to create a database and web interface for an assignment.
在其中一个表上有两列,两列都是外键,我需要使用它们作为主键。
On one of the tables I have two columns, both of which are foreign keys, and i need to use them both as the primary key.
这是迄今为止的代码:
drop database if exists testJoke;
create database testJoke;
use testJoke;
CREATE TABLE Author
(
id int(11) NOT NULL ,
name varchar(255) NULL ,
cust_email varchar(255) NULL,
password char(32) null,
PRIMARY KEY (id)
);
**CREATE TABLE AuthorRole
(
authorid int(11) NOT NULL ,
roleid varchar(255) NOT NULL,
PRIMARY KEY (authorid, roleid),
FOREIGN KEY(authorid) REFERENCES Author(id),
FOREIGN KEY(roleid) REFERENCES Role(id)
);**
CREATE TABLE Category
(
id int(11) NOT NULL ,
name varchar(255) NULL,
PRIMARY KEY (id)
);
CREATE TABLE Joke
(
id int(11) NOT NULL ,
joketext text NULL ,
jokedate date NOT NULL ,
authorid int(11) NULL,
PRIMARY KEY (id),
FOREIGN KEY(authorid) REFERENCES Author(id)
);
CREATE TABLE JokeCategory
(
jokeid int(11) NOT NULL ,
categoryid int(11) NOT NULL ,
PRIMARY KEY (jokeid, categoryid),
FOREIGN KEY(jokeid) REFERENCES Joke(id),
FOREIGN KEY(categoryid) REFERENCES Category(id)**
);
CREATE TABLE Role
(
id varchar(255) NOT NULL ,
description varchar(255) NULL ,
PRIMARY KEY (id)
);
所有表语法都与提供的数据字典一致。
All of the table syntax is in line with a data dictionary provided.
当我在mysql命令行中运行这个时,我在上面粗体突出的部分(表AuthorRole)上得到一个错误,说它不能添加外键约束。
When i run this in the mysql command line, i get an error on the section highlighted in bold above (table "AuthorRole"), saying that it "cannot add foreign key constraint".
我试过调试它,它似乎是:
I have had a try at debugging it, and it seems to be the:
FOREIGN KEY(roleid) REFERENCES Role(id)
导致问题的外键我删除它,一切都很好,如果我离开它,并删除其他外键,它给一个错误。)
Foreign key that is causing the problem (if i remove it, all works well, and if i leave it in and remove the other foreign key, it gives an error).
如果有人可以请解释我在哪里我会非常感谢。
If someone could please explain where i am going wrong, i would be very grateful.
我试过谷歌搜索这个,但没有找到任何东西(可能是因为我使用错误的关键字)。
I have tried googling this, but was unable to find anything (probably because i was using the wrong keywords).
感谢
干杯
Corey
Cheers Corey
推荐答案
首先创建表Role,然后创建表AuthorRole,并且它会正常
At first create the table "Role", then the table "AuthorRole" and it'll be ok
CREATE TABLE Role
(
id varchar(255) NOT NULL ,
description varchar(255) NULL ,
PRIMARY KEY (id)
);
CREATE TABLE AuthorRole
(
authorid int(11) NOT NULL ,
roleid varchar(255) NOT NULL,
PRIMARY KEY (authorid, roleid),
FOREIGN KEY(authorid) REFERENCES Author(id),
FOREIGN KEY(roleid) REFERENCES Role(id)
);
创建主键时最好使用 id INT NULL AUTO_INCREMENT
这篇关于使用两个外键作为主键 - MySQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!