Why I have "Can't create table '.\matchingup.frm' (errno: 150)" for such a simple table

Question

I have a table "MatchingUpRound" as followings:

    CREATE TABLE `matchingupround` (
  `eventID` int unsigned NOT NULL,
  `roundNo` tinyint unsigned NOT NULL,
  PRIMARY KEY  (`eventID`,`roundNo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then I try to create another table "MatchingUp":

   CREATE TABLE `matchingup` (
  `eventID` int unsigned NOT NULL,
  `roundNo` tinyint unsigned NOT NULL,
  `successfulMatching` boolean default false,
   primary key (eventID),
  Foreign key (roundNo) References MatchingUpRound (roundNo)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

But got this error Can't create table '.\matchingup.frm' (errno: 150). This is very strage?

am I doing something wrong?

possible duplicate of [MySQL: Can't create table (errno: 150)](http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150) — Anthony Neace, Oct 02 '14 at 00:25
In my particular situation, why? I even changed MatchingUp to MatchingUpDD, but still have error — Tum, Oct 02 '14 at 00:27
Also, there r many problem of 150, only 1 answer wont cover all errors — Tum, Oct 02 '14 at 00:28
As the duplicate suggests, it is almost assuredly a problem relating to your foreign keys. Examine related fields for discrepancies. For example, why is roundNo tinyint(3) in one table and tinyint in the other? Perhaps fix that? — Anthony Neace, Oct 02 '14 at 00:31
This may help you, to print the actual foreign key error: http://stackoverflow.com/a/24917068/775544. It will be beneficial to answerers to edit that into your question. — Anthony Neace, Oct 02 '14 at 00:38
@Haper, ur link doesnot answer, cos the roundNo need to be indexed, while other answer doesnot say anything about it — Tum, Oct 02 '14 at 00:46

score 3 · Accepted Answer · answered Oct 02 '14 at 00:42

The problem is the foreign key constraint, and the root cause is that there isn't suitable index on "roundno" in the referenced table.

If you run this:

CREATE INDEX matchingupround_IX1 ON matchingupround(roundno);

Then you can create the foreign key.

MySQL needs an index with "roundno" as the leading column. The other alternative is to re-order the columns in the PRIMARY KEY of the parent table. (Then the other index I suggested would be redundant, but you might want to add an index on the other column in the PRIMARY KEY.)

Normally, a foreign key references the PRIMARY KEY of the parent table. But, it can also reference a UNIQUE KEY. And MySQL is even more lenient, and allows any indexed column (or column(s)) to be referenced by a foreign key. In this case, it's possible for a child row to match multiple rows in the parent table (and that seems very odd to me.)

i put PRIMARY KEY (`roundNo`,`eventID`) in MatchingUpRound table and it works. Thank you very much for your help — Tum, Oct 02 '14 at 00:52
A small clarification: my answer said that MySQL needs an index... I'm thinking that this statement may not be entirely true. For tables using the InnoDB storage engine, it's true that InnoDB requires an index. But it may not be true for other storage engines. (For example, MyISAM doesn't enforce foreign key constraints, so MySQL may not care; there's several other storage engines. I think the error is actually being thrown by the InnoDB storage engine; MySQL is just relaying the error from InnoDB. — spencer7593, Oct 02 '14 at 01:00

Why I have "Can't create table '.\matchingup.frm' (errno: 150)" for such a simple table

1 Answers1