Laravel foreign key error 1822 when migrating and adding foreign key from pivot table

Viewed 61

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.

1 Answers

Make agreements_id unique in the table landlord_agreements

or add index

$table->index(['agreements_id']);
Related