使用两个外键作为主键- MySQL。

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.

我对MySQL很陌生(我必须为uni学习它)。我必须为一个任务创建一个数据库和一个web界面。

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.

在其中的一个表上，我有两个列，它们都是外键，我需要同时使用它们作为主键。

This is the code so far:

这是目前为止的代码:

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.

所有表语法都与提供的数据字典一致。

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".

当我在mysql命令行中运行它时，我在上面粗体突出显示的部分(表“AuthorRole”)上得到一个错误，说它“不能添加外键约束”。

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).

我试过在谷歌上搜索这个，但是找不到任何东西(可能是因为我用错了关键词)。

Thanks

谢谢

Cheers Corey

欢呼声科里

3 个解决方案

#1

At first create the table "Role", then the table "AuthorRole" and it'll be ok

首先创建表“Role”，然后创建表“AuthorRole”，这样就可以了

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)
);

And when creating primary keys it's better to use id INT(11) NOT NULL AUTO_INCREMENT

在创建主键时，最好使用id INT(11)而不是NULL AUTO_INCREMENT

#2

It is simply because you are referring to a primary key of the table which is not created at the time you refer to it, try the following sql script:

这仅仅是因为您引用的是表的主键，而在您引用该表时并没有创建主键，请尝试以下sql脚本:

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 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)

);



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 first and then AuthorRole.

首先创建表角色，然后创建AuthorRole。

#3

try changeing

试着变化

  roleid varchar(255) NOT NULL,

来

  roleid int(11) NOT NULL,

as the field and your key field are of different types

因为字段和键字段是不同的类型

#1