| Subject: | Problems with foreign key on MYSQL innodb and the solution for that |
DBIx::Class::DeploymentHandler::DeployMethod::SQL::Translator::try {...} (): DBI Exception: DBD::mysql::db do failed: Can't create table `...MyProject...`.`token` (errno: 150 "Foreign key constraint is incorrectly formed") [for Statement "CREATE TABLE `token` ( `id` integer NOT NULL auto_increment, `code_id` integer NOT NULL, INDEX `token_idx_code_id` (`code_id`), PRIMARY KEY (`id`), CONSTRAINT `token_fk_code_id` FOREIGN KEY (`code_id`) REFERENCES `code` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB"] at ...
SHOW ENGINE INNODB STATUS;
2015-10-30 13:49:41 7fc3fca90700 Error in foreign key constraint of table ...MyProject.../access_token_to_refresh_token:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
CONSTRAINT "access_token_to_refresh_token_fk_access_token_id_code_id" FOREIGN KEY ("access_token_id", "code_id") REFERENCES "token" ("id", "code_id") ON DELETE CASCADE ON UPDATE CASCADE
The index in the foreign key in table is "PRIMARY"
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
Solution:
packageCatalystX::OAuth2::Schema::Result::Token
...
__PACKAGE__->load_components( qw(DeploymentHooks::Indexing) );
__PACKAGE__->add_index(
idx_id_code_id => [qw(id code_id)],
);
...