title: Creating a MySQL table with two foreign keys in a cross-reference table
body: <p>This probably comes from some lack of knowledge or plain stupidity, but I would like to understand how to create foreign keys in a cross reference table:</p>
  
  <p>For example, I have two tables, item and image</p>
  
  <pre><code>CREATE TABLE `item` (
      `id` INT(11) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
      `name` VARCHAR(30) NOT NULL,
      `description` MEDIUMTEXT
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  CREATE TABLE `image` (
      `id` INT(11) UNSIGNED AUTO_INCREMENT,
      `file` VARCHAR(255) UNIQUE NOT NULL,
      `caption` VARCHAR(255),
      PRIMARY KEY(`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>I want to reference any image to any item, so I create a cross-reference table like this:</p>
  
  <pre><code>CREATE TABLE `item_image` (
      `item` INT(11) UNSIGNED NOT NULL,#foreign key
      `img` INT(11) UNSIGNED NOT NULL,#foreign key
      PRIMARY KEY(`item`,`img`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>But the item_image table contains columns that are foreign keys, so I try to constrain them like this</p>
  
  <pre><code>CREATE TABLE `item_image` (
      `item` INT(11) UNSIGNED NOT NULL,
      `img` INT(11) UNSIGNED NOT NULL,
      PRIMARY KEY(`item`,`img`),
      FOREIGN KEY(`item`)
          REFERENCES `item` (`id`) 
          ON DELETE CASCADE,
      FOREIGN KEY(`img`)
          REFERENCES `image` (`id`) 
          ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>So that if the image or the item get removed, the database would remove the cross reference too.</p>
  
  <p>I have read lots of resources on the web, and stackoverflow, and it is often suggested that there should be no problem in doing what I want
  <a href="https://stackoverflow.com/questions/4050784/defining-multiple-foreign-keys-in-one-table-to-many-tables">Defining multiple foreign keys in one table to many tables</a></p>
  
  <p>but MySQL gives me this generic error </p>
  
  <blockquote>
    <p>Error Code: 1005. Can't create table 'test.item_image' (errno: 150)</p>
  </blockquote>
  
  <p>I need some help to understand what exactly MySQL is complaining about.</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>I execute the queries in MySQL Workbench 5.2.39 CE if that matters...</p>
  
  <p>MySQL version: 5.5.16</p>