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

Question

mysql> ALTER TABLE category ADD CONSTRAINT category_parent_category_id FOREIGN KEY (parent) REFERENCES category(id);
ERROR 1005 (HY000): Can'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?

Roadbed

Answer 1

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

Lollipop

Answer 2

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.

Wigfall