mysql - ERROR 1005 (HY000): Can't create table 'test_schema.#sql-44c_2a' (errno: 150) -

so, i've recreated issue i'm having more readable code. run sql file in mysql prompt:

create schema if not exists test_schema  default character set utf8 collate utf8_general_ci ;  use test_schema ;  -- ----------------------------------------------------- -- table test_schema.table_one -- ----------------------------------------------------- create table if not exists table_one (     table_one_id int unsigned not null auto_increment,     table_one_name varchar(45) not null,     primary key (table_one_id, table_one_name) ) engine = innodb;  -- ----------------------------------------------------- -- table test_schema.table_two -- ----------------------------------------------------- create table if not exists table_two (     table_two_id int unsigned not null auto_increment,     table_two_name varchar(45) not null,     table_one_name varchar(45) not null,     primary key (table_two_id) ) engine = innodb;  alter table table_two add foreign key (table_one_name) references table_one(table_one_name); 

the output prompt is:

mysql> source test-database.sql; query ok, 1 row affected (0.00 sec)  database changed query ok, 0 rows affected (0.07 sec)  query ok, 0 rows affected (0.08 sec)  error 1005 (hy000): can't create table 'test_schema.#sql-44c_2a' (errno: 150) 

if run 'show engine innodb status;' following details

------------------------ latest foreign key error ------------------------ 150603  9:22:25 error in foreign key constraint of table test_schema/#sql-44c_2a: foreign key (table_one_name) references table_one(table_one_name): cannot find index in referenced table referenced columns appear first columns, or column types in table , referenced table not match constraint. note internal storage type of enum , set changed in tables created >= innodb-4.1.12, , such columns in old tables cannot referenced such columns in new tables. see http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html correct foreign key definition. 

i've searched issue , every 1 it's about:
1) not having same type
- they're both "varchar(45) not null" (tried letting 1 in table_two null, didn't alter error-message)
2) foreign key not being primary key
- table_one_name primary key in table_one table_one_id
3) make sure charset , collate options same both @ table level
- mean? suppose since don't change them?

please // finbel

in order add foreign key 2 fields must indexed.

try this:

-- ----------------------------------------------------- -- table test_schema.table_one -- ----------------------------------------------------- create table if not exists table_one (     table_one_id int unsigned not null auto_increment,     table_one_name varchar(45) not null,     primary key (table_one_id, table_one_name),     key `one_name` (`table_one_name`) ) engine = innodb;  -- ----------------------------------------------------- -- table test_schema.table_two -- ----------------------------------------------------- create table if not exists table_two (     table_two_id int unsigned not null auto_increment,     table_two_name varchar(45) not null,     table_one_name varchar(45) not null,     primary key (table_two_id),     key `two_name` (`table_two_name`) ) engine = innodb;  alter table table_two  add foreign key (table_one_name) references table_one(table_one_name);