What's wrong with the Foreign Key Constraint in this table?

MySQL 5.1.59 throws an error with this create table:

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;

which w开发者_如何转开发as generated by MySQLWorkbench 5.2.33. The error message is:

ERROR 1005 (HY000) at line __: Can't create table 'mydb.genre' (errno: 150)

What's wrong with this create table?

The manual says:

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.

It also says that foreign key references to the same table are allowed:

InnoDB supports foreign key references within a table. In these cases, “child table records” really refers to dependent records within the same table.

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.

It may be relevant that MySQLWorkbench set the following:

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

Your column id is int unsigned; your column parent_id is int. Those don't match. The solution is to change parent_id to be int unsigned as well.

If you run the SHOW ENGINE InnoDB STATUS I put in a comment, you see this:

11005 17:18:38 Error in foreign key constraint of table test/genre:

    FOREIGN KEY (`parent_id` )
    REFERENCES `genre` (`id` )
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

Note the "column types in the table and the referenced table do not match" part.

The two fields are not the same type.

CREATE  TABLE IF NOT EXISTS `genre` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,  <<-- unsigned int
  ..
  ..
  `parent_id` INT NULL DEFAULT NULL ,        <<-- signed int
  PRIMARY KEY (`id`) ,                   ***** not the same!!!!
  ....

id is UNSIGNED but parent_id is not unsigned.

The fields must be the same type. id is unsigned whereas parent_id is not