Error No 150 mySQL

Question

Hi all. This is making me sweat - I am getting error 150 when I try and create a table in mySQL. I've scoured the forums to no avail. The statement uses foreign key constraints - both tables are InnoDB, all relevant columns have the same data type and both tables have the same charset and collation. Here's the CREATE TABLE and the original CREATE TABLE statement for the table that's being referenced. 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;

Referenced table:

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

Update: I must have been blind:

The reason why your create table failes, is because your "rev_page_id" key is in the wrong order (innodb requires the key to be the same order as the foreign key:

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`),
  ....

Please ignore suggestion below:

---

Try

SHOW ENGINE INNODB STATUS

Look for smth. like "LAST INNODB FOREIGN KEY ERROR" Unfortunatly the error message isn't very intutive most of the time. Since you said the types are all the same I would guess it could be the following error:

• The FK name already exists (FK names must be unique accross the Database). To be true they have to be unique for the whole mysql instance, but internally innodb adds the database name to the foreign key name (FK_approval_user is internally called yourdbname#FK_approval_user)

Update:

It looks like you are missing the key for "FK_approval_revision"

Should look like this:

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 2

This error is typically related to foreign key restrictions. Execute show innodb status and look for the LATEST FOREIGN KEY ERROR section to get some more concrete explanation.

This is what I get when creating the second table:

Error in foreign key constraint of table test/approval: 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: 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.

Answer 3

it will work if you change the order of columns in constraint definition:

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

Your original query doesn't work because the order of fields must be the same as in unique index. However, adding rev_page column to constraint is superfluous (revision.rev_id + revision.rev_page is 100% unique without a unique constraint, since revision.rev_id unique by itself). So you don't need unique key on (revision.rev_id + revision.rev_page) , and it would be much better if you change your constraint to

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