[MySQL-errors] ERROR 1005 (HY000) at line 28: Can’t create table ‘X’ (errno: 150)

Spent quite a bit of time trying to figure this one out. It turns out, InnoDB wants an index created on a referenced field. It turns out, the error was as a result of a non-indexed referenced field–title_etd in this case.
Affected tables are below

create table Title
(title_etd varchar(255),
oai_identifier varchar(255),
primary key (oai_identifier)
);

create table Subject
(subject_etd varchar(255),
oai_identifier varchar(255),
title_etd varchar(255),
constraint subject_title_etd foreign key (title_etd) references Title(title_etd),
constraint subject_oai_identifier foreign key (oai_identifier) references Title(oai_identifier),
primary key (oai_identifier,title_etd,subject_etd)
);

MySQL InnoDB status output is below.

mysql>
mysql> SHOW ENGINE INNODB STATUS\G
:
:
RW-excl spins 0, rounds 1098, OS waits 36
Spin rounds per wait: 12.35 mutex, 30.00 RW-shared, 1098.00 RW-excl
------------------------
LATEST FOREIGN KEY ERROR
------------------------
140110 13:16:42 Error in foreign key constraint of table test_etd_dbp/Subject:
foreign key (title_etd) references Title(title_etd),
constraint subject_oai_identifier foreign key (oai_identifier) references Title(oai_identifier),
primary key (oai_identifier,title_etd,subject_etd)
):
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.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
------------
TRANSACTIONS
------------
Trx id counter 1321
:
:
1 row in set (0.04 sec)

mysql>

I had to create an index on Title.title_etd to resolve this.

mysql> ALTER TABLE Title ADD INDEX idx_title_etd (title_etd);
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
Categories: Technical
Tags: ,