'Foreign key constraint is incorrectly formed' exception in laravel

by ganesh   Last Updated October 10, 2019 10:26 AM

I am learning the laravel. I am stuck at creating for foreign key to table. I have 3 tables agricultural_produce_market_commettee, category_master, traders. I want to use primary keys of agricultural_produce_market_commettee, category_master as a foreign key to traders table.

I have created tables in order like

  1. category_master

  2. agricultural_produce_market_commettee

  3. traders

My category_master table is as follows:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateCategoryMastersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('category_master', function (Blueprint $table) {
            $table->bigIncrements('category_id');
            $table->string('category_name');
            $table->boolean('is_active');
            $table->bigInteger('created_by');
            $table->bigInteger('updated_by');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('category_master');
    }
}

My agricultural_produce_market_commettee table is as follows:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateAgriculturalProduceMarketCommetteesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('agricultural_produce_market_commettee', function (Blueprint $table) {
            $table->bigIncrements('apmc_id');
            $table->string('apmc_branch_name');
            $table->string('apmc_city_name');
            $table->boolean('is_active');
            $table->bigInteger('created_by');
            $table->bigInteger('updated_by');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('agricultural_produce_market_commettee');
    }
}

My traders table is as follows:

       <?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateTradersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('traders', function (Blueprint $table) {
            $table->bigIncrements('traders_id');
            $table->bigInteger('category_id');
                $table->foreign('category_id')->references('category_id')->on('category_master')->onDelete('cascade');
                $table->bigInteger('apmc_id');
                $table->foreign('apmc_id')->references('apmc_id')->on('agricultural_produce_market_commettee')->onDelete('cascade');
                $table->string('traders_name');
                $table->string('traders_address');
                $table->string('traders_contact_number');
                $table->boolean('is_active');
                $table->bigInteger('created_by');
                $table->bigInteger('updated_by');
                $table->timestamps();

        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('traders');
    }
}

Datatypes of primary keys apmc_id, category_id are bigInteger.

when I migrate traders table it shows me

 Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1005 Can't create table `cms`.`#sql-32b8_1d6` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `traders` add constraint `traders_category_id_foreign` foreign key (`category_id`) references `category_master` (`category_id`) on delete cascade)

But howerver it creates traders table in my database with apmc_id, category_id columns.

I tried multiple solutions but not able to resolve this error. please guide me what went wrong?

Tags : php mysql laravel


Answers 1


The agricultural_produce_market_commettee and category_master tables have to be migrated BEFORE traders so change the filename date timestamps for that and then run

php artisan dumpautoload
php artisan migrate:refresh

And the foreign key has to match the incremental ID

$table->unsignedBigInteger('apmc_id');

Also the created_at and updated_at columns are useless, you already get that from timestamps() so remove them

Hope this helps

Saly 3301
Saly 3301
October 10, 2019 10:23 AM

Related Questions


LARAVEL 5.5 Foreign key constraint is incorrectly formed'

Updated September 11, 2017 03:26 AM



How can I add condition in db raw laravel?

Updated October 04, 2017 07:26 AM

laravel filter the results based upon attributes

Updated April 30, 2018 08:26 AM