title: Using two foreign keys as a primary key - MySQL
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>This is the code so far:</p>
  
  <pre><code>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)
  );
  </code></pre>
  
  <p>All of the table syntax is in line with a data dictionary provided.</p>
  
  <p>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".</p>
  
  <p>I have had a try at debugging it, and it seems to be the:</p>
  
  <pre><code>FOREIGN KEY(roleid) REFERENCES Role(id)
  </code></pre>
  
  <p>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).</p>
  
  <p>If someone could please explain where i am going wrong, i would be very grateful.</p>
  
  <p>I have tried googling this, but was unable to find anything (probably because i was using the wrong keywords).</p>
  
  <p>Thanks </p>
  
  <p>Cheers
  Corey</p>