MySQL foreign key to the same table failed with error 1005, errno 150

mysql> ALTER TABLE category ADD CONSTRAINT category_parent_category_id FOREIGN KEY (parent) REFERENCES category(id);
ERROR 1005 (HY000): C开发者_运维技巧an't create table 'sfnews.#sql-244_1' (errno: 150)

DDL as follows:

Create Table: CREATE TABLE `category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `parent` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `parent_idx` (`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Why is it wrong?

Self reference should be possible. It's because "parent" is unsigned and "id" is not. Change the table definitions id column to

 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

and it will work.

The reference states about foreign keys: "The size and sign of integer types must be the same"

Seems to be the same problem described here

If you check the status of the InnoDB engine (SHOW ENGINE InnoDB STATUS), you'll get a fuller explanation:

LATEST FOREIGN KEY ERROR

[...]

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.

Make id unsigned.