Laravel Migration issues – Foreign Key not able to be assigned.

1. First issue, not using INNODB (using MYISAM by default). To fix this, each table will need to have this line

Schema::create(‘branches’, function (Blueprint $table) {

$table->engine = ‘InnoDB’;

 

2. MySQL Error 1215: Cannot add foreign key constraint

To debug this, run the following SQL against the DB.

SHOW ENGINE INNODB STATUS

————————
LATEST FOREIGN KEY ERROR
————————
2017-10-01 19:01:01 0x29fc Error in foreign key constraint of table amanah/#sql-b98_157:
foreign key (`brnch_id`) references `branches` (`brnch_id`) on delete cascade:
Cannot resolve table name close to:
(`brnch_id`) on delete cascade

 

This usually means the system was not able to find the TABLE mentioned .

Also this would mean the table is NOT YET BUILT when the assignment of Foreign key is done.

To fix this issue: Check the date and time of each migration file (for each table). The table referenced to have the foreign key MUST BE BUILT BEFORE. Therefore the filenames MUST BE IN ORDER.

 

Migrating: 2017_09_23_000000_create_users_table
Migrated: 2017_09_23_000000_create_users_table
Migrating: 2017_09_23_100000_create_password_resets_table
Migrated: 2017_09_23_100000_create_password_resets_table
Migrating: 2017_10_01_141720_create_branches_table
Migrated: 2017_10_01_141720_create_branches_table
Migrating: 2017_10_01_151153_create_mettingtypes_table
Migrated: 2017_10_01_151153_create_mettingtypes_table
Migrating: 2017_10_01_215348_create_reports_table
Migrated: 2017_10_01_215348_create_reports_table
Migrating: 2017_10_02_215258_create_meetings_table
Migrated: 2017_10_02_215258_create_meetings_table

 

Once that is fixed, MIGRATION TABLE NEEDS TO BE TRUNCATED (so that it will contain the updated filenames).

$table->integer(‘brnch_id’)->unsigned();
$table->foreign(‘mtg_apprvl_user’)->references(‘user_id’)->on(‘users’)->onDelete(‘cascade’);
$table->foreign(‘brnch_id’)->references(‘brnch_id’)->on(‘branches’)->onDelete(‘cascade’);
$table->foreign(‘mtg_type_descr’)->references(‘mtg_type_descr’)->on(‘meetingtypes’)->onDelete(‘cascade’);

 

The code should  be as above.

 

The foreign key must be of the same type (unsigned integer) and length (10) – else it wont work.

 

Also, the foreign key (in the original/main table) must be added as INDEXED, or PRIMARY.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *