CODEWITHSUNDEEP
phpforeign-keyslaravel-8laravel-migrations
user14777223
user14777223

Reputation:

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

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)

Upvotes: 0

Views: 1931

Answers (2)

Mahmoud Ow
Mahmoud Ow

Reputation: 446

laravel migrations files contains a datetime slug that determine which file will be migrated first

notice the order

2021_10_11_101533_create_posts_table.php
2014_10_12_000000_create_users_table.php

when you run

php artisan migrate

posts table will be migrated first and it contains a foriegn key

user_id

which references a primary key on users table

id

but the users table doesn't exists yet, to fix this issues change the files names and make sure users table migrate first like this

notice the order

2014_10_10_000000_create_users_table.php
2021_10_11_101533_create_posts_table.php

short pattern for declaring a foreign key

$table->foreignId('user_id')->constrained()->cascadeOnDelete();

Upvotes: 0

Donkarnash
Donkarnash

Reputation: 12835

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()

Upvotes: 4

Related Questions