mysql 两个外键组成主键,使用两个外键作为主键 - 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.

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

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

解决方案

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)

);

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