I have two tables. The first one is a pivot table (with two composite keys). I want to add a foreign key in my second table and that foreign key is based on the pivot table. When I run the migration it causes an exception
SQLSTATE[HY000]: General error: 1822 Failed to add the foreign key constraint.
Missing index for constraint 'property_chqeque_first_rows_landlord_agreement_id_foreign'
in the referenced table 'landlord_agreements' (SQL: alter table`property_chqeque_first_rows`
add constraint
`property_chqeque_first_rows_landlord_agreement_id_foreign` foreign key
(`landlord_agreement_id`) references `landlord_agreements` (`agreements_id`))
Here is my two migration files
pivot table
Schema::create('landlord_agreements', function (Blueprint $table) {
$table->primary(['landlords_id', 'agreements_id']);
$table->unsignedBigInteger('landlords_id');
$table->unsignedBigInteger('agreements_id');
$table->string('created_by');
$table->string('updated_by')->nullable();
$table->timestamps();
$table->softDeletes();
});
The migration for adding the column and foreign key in second table
Schema::table('property_chqeque_first_rows', function (Blueprint $table) {
$table->unsignedBigInteger('landlord_agreement_id')->after('id')->nullable();
$table->foreign('landlord_agreement_id')->references('agreements_id')->on('landlord_agreements');
});
Can someone tell me where I did go wrong? I always use this method when I create a foreign key but this time it doesn't migrate. I was thinking to just add a column without a foreign key link.