使用两个外键作为主键

本文介绍了使用两个外键作为主键 - 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