title: What's wrong with the Foreign Key Constraint in this table?
body: <p>MySQL 5.1.59 throws an error with this create table:</p>
  
  <pre><code>CREATE  TABLE IF NOT EXISTS `genre` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `abv` CHAR(3) CHARACTER SET 'latin1' COLLATE 'latin1_bin' NULL DEFAULT NULL ,
    `name` VARCHAR(80) NOT NULL DEFAULT '' ,
    `parent_id` INT NULL DEFAULT NULL ,
    PRIMARY KEY (`id`) ,
    INDEX `fk_genre_genre1` (`parent_id` ASC) ,
    CONSTRAINT `fk_genre_genre1`
      FOREIGN KEY (`parent_id` )
      REFERENCES `genre` (`id` )
      ON DELETE SET NULL
      ON UPDATE CASCADE)
  ENGINE = InnoDB;
  </code></pre>
  
  <p>which was generated by MySQLWorkbench 5.2.33. The error message is:</p>
  
  <blockquote>
    <p>ERROR 1005 (HY000) at line __: Can't create table 'mydb.genre' (errno: 150)</p>
  </blockquote>
  
  <p>What's wrong with this create table?</p>
  
  <p><a href="http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html" rel="nofollow noreferrer">The manual says</a>:</p>
  
  <blockquote>
    <p>If MySQL reports an error number 1005 from a CREATE TABLE statement,
    and the error message refers to error 150, table creation failed
    because a foreign key constraint was not correctly formed.</p>
  </blockquote>
  
  <p>It also says that foreign key references to the same table are allowed:</p>
  
  <blockquote>
    <p>InnoDB supports foreign key references within a table. In these cases,
    “child table records” really refers to dependent records within the
    same table.</p>
  </blockquote>
  
  <p>The relationship I want is a non-identifying parent-child to represent a hierarchy of genres and sub-genres. A genre doesn't have to have a parent, hence parent_id is nullable.</p>
  
  <p>It may be relevant that MySQLWorkbench set the following:</p>
  
  <pre><code>SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
  </code></pre>