Error No 150 mySQL

Question

It makes me sweat - I get an error of 150 when I try to create a table in mySQL. I browsed the forums to no avail. The statement uses foreign key constraints - both tables are InnoDB, all the corresponding columns have the same data type, and both tables have the same encoding and sorting. Here is the CREATE TABLE and the original CREATE TABLE statement for the referenced table. Any ideas?

New table:

CREATE TABLE `approval` (
  `rev_id` int(10) UNSIGNED NOT NULL,
  `rev_page` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (`rev_id`,`rev_page`,`user_id`),
  KEY `FK_approval_user` (`user_id`),
  CONSTRAINT `FK_approval_revision` FOREIGN KEY (`rev_id`, `rev_page`) REFERENCES `revision` (`rev_id`, `rev_page`),
  CONSTRAINT `FK_approval_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Link:

CREATE TABLE `revision` (
  `rev_id` int(10) unsigned NOT NULL auto_increment,
  `rev_page` int(10) unsigned NOT NULL,
  `rev_text_id` int(10) unsigned NOT NULL,
  `rev_comment` tinyblob NOT NULL,
  `rev_user` int(10) unsigned NOT NULL default '0',
  `rev_user_text` varbinary(255) NOT NULL default '',
  `rev_timestamp` binary(14) NOT NULL default '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `rev_minor_edit` tinyint(3) unsigned NOT NULL default '0',
  `rev_deleted` tinyint(3) unsigned NOT NULL default '0',
  `rev_len` int(10) unsigned default NULL,
  `rev_parent_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`rev_id`),
  UNIQUE KEY `rev_page_id` (`rev_page`,`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=4904 DEFAULT CHARSET=binary MAX_ROWS=10000000 AVG_ROW_LENGTH=1024;

Answer 1

This error is usually related to foreign key constraints. Follow show innodb statusand find the LAST FOREIGN KEY section for more specific explanations.

, :

/: FOREIGN KEY (rev_id, rev_page) revision (rev_id, rev_page),
CONSTRAINT FK_approval_user (user_id) user(user_id)) ENGINE = InnoDB DEFAULT CHARSET = latin1: , .

Answer 2

: , , :

, , , "rev_page_id" (innodb , , :

CREATE TABLE `revision` (
  `rev_id` int(10) unsigned NOT NULL auto_increment,
  ....
  `rev_parent_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`rev_id`),
  -- wrong:
  -- UNIQUE KEY `rev_page_id` (`rev_page`,`rev_id`), 
  -- better:
  UNIQUE KEY `rev_page_id` (`rev_id`, `rev_page`),
  ....

, :

---

Try

SHOW ENGINE INNODB STATUS

-. " INNODB" , . , , , :

• FK ( FK ). , mysql, innodb (FK_approval_user yourdbname # FK_approval_user)

Update:

, "FK_approval_revision"

:

CREATE TABLE `approval` (
  `rev_id` int(10) UNSIGNED NOT NULL,
  `rev_page` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (`rev_id`,`rev_page`,`user_id`),
  KEY `FK_approval_user` (`user_id`),
  KEY `FK_approval_revision` (`rev_id`, `rev_page`),
  CONSTRAINT `FK_approval_revision` FOREIGN KEY (`rev_id`, `rev_page`) REFERENCES `revision` (`rev_id`, `rev_page`),
  CONSTRAINT `FK_approval_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 3

, :

FOREIGN KEY (`rev_page`,`rev_id`) REFERENCES `revision` (`rev_page`,`rev_id`)

Your original query does not work because the field order must be the same as in the unique index. However, adding the rev_page column to the constraint is redundant (the rev.rev_id + revision.rev_page version is 100% unique without a unique constraint, since the version.rev_id version itself is unique). This way you don't need a unique key on (revision.rev_id + revision.rev_page), and it would be much better if you change your restriction to

FOREIGN KEY (`rev_id`) REFERENCES `revision` (`rev_id`)

Answer 4

show innodb status

this cmd will help a lot