SQLSTATE[HY000]: General error: 1005 Can't create table Laravel 8

Question

Schema::create('menus', function (Blueprint $table) {
    $table->id();
    $table->string('name')->unique();
    $table->string('slug')->unique();
    $table->integer('price');
    $table->text('description');
    $table->timestamps();
});

Schema::create('categories', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name')->unique();
    $table->string('slug')->unique();
    $table->timestamps();
});

Schema::create('category_menu', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('menu_id')->unsigned()->nullable();
    $table->foreign('menu_id')->references('id')
        ->on('menus')->onDelete('cascade');
    $table->integer('category_id')->unsigned()->nullable();
    $table->foreign('category_id')->references('id')
        ->on('categories')->onDelete('cascade');
    $table->timestamps();
});

When I run php artisan:migrate, I get the following error.

SQLSTATE[HY000]: General error: 1005 Can't create table `mieaceh`.`category_menu` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `category_menu` add constraint `category_menu_menu_id_foreign` foreign key (`menu_id`) references `menus` (`id`) on delete cascade)

Donkarnash Donkarnash · Accepted Answer · 2020-12-09T13:48:57

This is due to mismatch of datatype of foreign key column and the referenced column most likely

When you create a primary key with $table->id() the datatype of the auto incrementing id column is unsignedBigInteger so you must have foreign key also with the same datatype

Schema::create('category_menu', function (Blueprint $table) {
        $table->id();

        $table->unsignedBigInteger('menu_id');
        $table->foreign('menu_id')->references('id')
            ->on('menus')->onDelete('cascade');

        $table->unsignedBigInteger('category_id');
        $table->foreign('category_id')->references('id')
            ->on('categories')->onDelete('cascade');

        $table->timestamps();
});

You shouldn't make the columns in a pivot table nullable for the foreign keys to maintain data integrity.

Also be consistent with the datatype for primary key columns as well as definitions - when using $table->id() keep that consistent across all migrations for all tables. That way you will have less chances of mismatch when defining foreign keys as $table->unsignedBigInteger()

SQLSTATE[HY000]: General error: 1005 Can't create table Laravel 8

1 Answers