CODEWITHSUNDEEP
laravelmariadb
Muhammad Tareq
Muhammad Tareq

Reputation: 1278

How to fix error on Foreign key constraint incorrectly formed in migrating a table in Laravel

When migrating my DB, this error appears. Below is my code followed by the error that I am getting when trying to run the migration.

Code

public function up()
{
    Schema::create('meals', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('user_id')->unsigned();
        $table->integer('category_id')->unsigned();
        $table->string('title');
        $table->string('body');
        $table->string('meal_av');
        $table->timestamps();
        $table->foreign('user_id')
            ->references('id')
            ->on('users')
            ->onDelete('cascade');
        $table->foreign('category_id')
            ->references('id')
            ->on('categories')
            ->onDelete('cascade');
    });
}

Error message

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1005 Can't create table meal.#sql-11d2_1 4 (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter
table meals add constraint meals_category_id_foreign foreign key (category_id) references categories (id) on delete cascade)

Upvotes: 71

Views: 140698

Answers (29)

Chimaobi Ezeobidi
Chimaobi Ezeobidi

Reputation: 61

If you are still encountering the issue use this

  $table->foreignId('project_id')
                ->constrained()
                ->onUpdate('cascade')
                ->onDelete('cascade');

It will just look up for that table and add a foreign key to it, it works.

Upvotes: 0

Two
Two

Reputation: 658

Using Laravel 9~

I fixed mine by setting the id to increments, and assigned the foreign key to unsignedInteger, and then sort the migration file its the corresponding order.

My solution was based from @Swooth and @Muhammad Tareq solution

public function up()
{
    Schema::create('cart_items', function (Blueprint $table) {
        $table->increments('id');
        $table->unsignedInteger('session_id')->index();
        $table->foreign('session_id')->references('id')->on('shopping_sessions')
            ->onDelete('cascade');
}

Upvotes: -1

light
light

Reputation: 11

You just need to create your migrations in order. Make sure you create the tables that don't receive any foreign keys first. Then create the ones that do. And if you have already created your migrations, just change the time or date of your migrations so that tables that do not receive any foreign keys that are created before those that do.

Upvotes: -2

MosesK
MosesK

Reputation: 359

Am using Laravel 8 and had the same error. The issue is that a both those columns eg users.id and meals.user_id where user_id is the foreign key need to be the same.

The users.id looks like this:

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
});

In mySql id is an Int(10) unsinged AUTO ICREMENT.

If we go to a different table where we want to set a foreign key e.g. the one below I changed the user_id to be an unsigned() also. Previously I had written it as simply $table->integer('user_id') and this gave me the exception but now you won't encounter that error because they are both Int(10) and Unsigned:

Schema::create('users_permissions', function (Blueprint $table) {
    $table->integer('user_id')->unsigned();
    $table->integer('permission_id')->unsigned();

    //Foreign Key Constraints
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    $table->foreign('permission_id')->references('id')->on('permissions')->onDelete('cascade');



    //Setting the primary keys
    $table->primary(['user_id','permission_id']);


});

Upvotes: 3

Víctor Droguett Méndez
Víctor Droguett Méndez

Reputation: 1

In my case, the problem was the difference between the table's engines. In my referenced table I didn't specify the engine.

It doesn't work

// Referenced table
Schema::create('budgets', function (Blueprint $table) {
        $table->id();
        $table->timestamps();
        $table->softDeletes();
});

// The other table
    Schema::create('payment', function (Blueprint $table) {
        $table->engine = 'InnoDB';
        $table->integer('budget_id')->unsigned()->nullable();
        $table->foreign('budget_id')
            ->references('id')
            ->on('budgets')
            ->onDelete('cascade');
        $table->timestamps();
});

To keep it under control, I recommend setting the engine on all your migrations to create tables. (Don't trust default database settings)

It works

// Referenced table
Schema::create('budgets', function (Blueprint $table) {
        $table->engine = 'InnoDB';
        $table->id();
        $table->timestamps();
        $table->softDeletes();
});

// The other table
    Schema::create('payment', function (Blueprint $table) {
        $table->engine = 'InnoDB';
        $table->integer('budget_id')->unsigned()->nullable();
        $table->foreign('budget_id')
            ->references('id')
            ->on('budgets')
            ->onDelete('cascade');
        $table->timestamps();
});

Upvotes: -1

ropehapi
ropehapi

Reputation: 99

I had the same problem , so i changed the creation date of my migration , changing this , i changed the execution order of the migrations , and the needed table was created first of the table i used it as a foreign key

Upvotes: 1

Ramananda Panda
Ramananda Panda

Reputation: 55

I just added

$table->engine = 'MyISAM';

It worked. It is because laravel by default creates tables with InnoDB Engine.

Upvotes: -1

Safaetul Ahasan Piyas
Safaetul Ahasan Piyas

Reputation: 1773

I had to face the same problem at Laravel 6. I solve this following way.

I think it helps you or others:

    $table->bigIncrements('id');
    $table->bigInteger('user_id')->unsigned(); //chnage this line
    $table->bigInteger('category_id')->unsigned(); //change this line
    ---
    $table->foreign('user_id')
        ->references('id')
        ->on('users')
        ->onDelete('cascade');
    $table->foreign('category_id')
        ->references('id')
        ->on('categories')
        ->onDelete('cascade');

Incrementing ID using a "big integer" equivalent.

used bigInteger instead of Integer

  1. If still now you got an error.

I suggest you reorder your migration file following ways:

Change the dates that form the first part of the migration filenames So they're in the order you want (example: for 2020_07_28_133303_update_categories.php, the date & time is 2020-07-28, 13:33:03);

N.B: First must be 'categories' migration file than 'meals' migration File.

N.B: In Laravel 5.6, for $table->increments('id'); use $table->integer('user_id')->unsigned();

Upvotes: 6

geekbro
geekbro

Reputation: 1323

Just add ->unsigned()->index() at the end of the foreign key and it will work.

Upvotes: 16

Muhammad Tareq
Muhammad Tareq

Reputation: 1278

@JuanBonnett’s question has inspired me to find the answer. I used Laravel to automate the process without considering the creation time of the file itself. According to the workflow, “meals” will be created before the other table (categories) because I created its schema file (meals) before categories. That was my fault.

Upvotes: 49

Hussam Adil
Hussam Adil

Reputation: 540

Laravel 5.8

In the foreign key column use unsignedBigInteger to avoid mismatch foreign key data type problem . For example let us assume we have two tables questions and replies
Questions table will look:

 public function up()
    {
        Schema::create('questions', function (Blueprint $table) {
            $table->bigIncrements('id');
             $table->text('body');
             $table->integer('user_id')->unsigned();
            $table->timestamps();
        });
    }

Replies table look like :

public function up()
{
    Schema::create('replies', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->text('body');
        $table->unsignedBigInteger('question_id');
        $table->integer('user_id')->unsigned();
        $table->foreign('question_id')->references('id')->on('questions')->onDelete('cascade');
        $table->timestamps();
    });
}

Upvotes: 8

Yziaf_07
Yziaf_07

Reputation: 7

  1. Check in your database reference table must have primary key && auto increment
  2. Drop the table which you want to migrate and Run the migrate again

Upvotes: -1

mujuonly
mujuonly

Reputation: 11861

Laravel 6: Update on 17 Jan 2020

$table->bigInteger( 'category_id' )->unsigned();

This worked well for me

Upvotes: 5

Rafael Xavier
Rafael Xavier

Reputation: 1083

Please add ->nullable() on your field and make sure that all the fields you're referring to really exist.

Upvotes: -1

mubasshir00
mubasshir00

Reputation: 349

I just use $table->unsignedBigInteger('user_id'); and solve it . (laravel 5.8)

Upvotes: 1

Felipe Araujo
Felipe Araujo

Reputation: 41

One way to get around foreign key errors is to disable checking: "SET FOREIGN_KEY_CHECKS". This is a palliative solution, but the correct thing is really to adjust the tables and their relationships. 

   DB::statement('SET FOREIGN_KEY_CHECKS=0;');

   Schema::table('example', function (Blueprint $table) {

        $table->integer('fk_example')->unsigned()->index();
        $table->foreign('fk_example')->references('id')->on('examples');
    });

  DB::statement('SET FOREIGN_KEY_CHECKS=1;');

Upvotes: 2

Isaac Tetteh
Isaac Tetteh

Reputation: 1

It is a simple question, so give a simple answer and stop beating about the bush, change your example $table->integer('user_id')->unsigned(); to $table->BigInteger('user_id')->unsigned(); to solve the foreign key error. so change integer to BigInteger in the migration file...

Upvotes: -1

Bashar
Bashar

Reputation: 473

I got the same message for data type miss-matched problem.

I used bigIncrements() for 'id' and when I used it as foreign key (used bigInteger()) I got the error.

I have found the solution, bigIncrements() returns unsignedBigInteger. So need to use unsignedBigInteger() instead of bigInteger() in foreign key

Sharing this because it might help others

Upvotes: 17

Shahin Akbari
Shahin Akbari

Reputation: 29

You should first create Categories and users Table when create "meals"

To solve the issue you should rename migration files of Category and Users to date of before Meals Migration file that create those before Meals table.

sample:

2019_04_10_050958_create_users_table 
2019_04_10_051958_create_categories_table
2019_04_10_052958_create_meals_table

Upvotes: -1

Marek
Marek

Reputation: 149

Remember that this is important the referenced and referencing fields must have exactly the same data type.

Upvotes: -1

Swooth
Swooth

Reputation: 1176

When creating a new table in Laravel. A migration will be generated like:

$table->bigIncrements('id');

Instead of (in older Laravel versions):

$table->increments('id');

When using bigIncrements the foreign key expects a bigInteger instead of an integer. So your code will look like this:

public function up()
    {
        Schema::create('meals', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedBigInteger('user_id'); //changed this line
            $table->unsignedBigInteger('category_id'); //changed this line
            $table->string('title');
            $table->string('body');
            $table->string('meal_av');
            $table->timestamps();

            $table->foreign('user_id')
                ->references('id')
                ->on('users')
                ->onDelete('cascade');

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

You could also use increments instead of bigIncrements like Kiko Sejio said.

The difference between Integer and BigInteger is the size:

  • int => 32-bit
  • bigint => 64-bit

Upvotes: 109

Kiko Seijo
Kiko Seijo

Reputation: 701

In my case, the new laravel convention was causing this error.

Just by a simple swap of the table creation id did the trick.

$table->increments('id'); // ok

, instead of:

$table->bigIncrements('id'); // was the error.

Already working with Laravel v5.8, never had this error before.

Upvotes: 6

Ayenew Yihune Demeke
Ayenew Yihune Demeke

Reputation: 1277

The order of creation of migration files should be sorted and the foreign key should have exactly similar property as the primary key in the other table.

Upvotes: 0

Napster Scofield
Napster Scofield

Reputation: 1289

You should create your migration in order for example I want my users to have a role_id field which is from my roles table

I first start to make my role migration php artisan make:migration create_roles_table --create=roles

then my second user migration php artisan make:migration create_users_table --create=users

php artisan migration will execute using the order of the created files 2017_08_22_074128_create_roles_table.php and 2017_08_22_134306_create_users_table check the datetime order, that will be the execution order.

files 2017_08_22_074128_create_roles_table.php

public function up()
{
    Schema::create('roles', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name', 50);
        $table->timestamps();
    });
}

2017_08_22_134306_create_users_table

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('role_id')->unsigned();
        $table->string('name');
        $table->string('phone', 20)->unique();
        $table->string('password');
        $table->rememberToken();
        $table->boolean('active');
        $table->timestamps();
        $table->foreign('role_id')->references('id')->on('roles');
    });
}

Upvotes: 25

aphoe
aphoe

Reputation: 2716

if you are using ->onDelete('set null') in your foreign key definition make sure the foreign key field itself is nullable() ie

//Column definition
$table->integer('user_id')->unsigned()->index()->nullable(); //index() is optional

//...
//...

//Foreign key 
$table->foreign('user_id')
      ->references('id')
      ->on('users')
      ->onDelete('set null');

Upvotes: 12

zamppe
zamppe

Reputation: 151

For me everything was in correct order, but it still didn't work. Then I found out by fiddling that the primary key must be unsigned.

//this didn't work
$table->integer('id')->unique();
$table->primary('id');

//this worked
$table->integer('id')->unsigned()->unique();
$table->primary('id');

//this worked 
$table->increments('id');

Upvotes: 15

Matthias
Matthias

Reputation: 31

Maybe it can be of help to anyone landing here : I just experienced this same issue, and in my case it was that I had a (composite) unique constraint set on the foreign key column BEFORE the foreign key constraint. I resolved the issue by having the "unique" statement placed AFTER the "foreign" statement.

Works:

$table->foreign('step_id')->references('id')->on('steps')->onDelete('cascade');
$table->unique(['step_id','lang']);

Doesn't work:

$table->unique(['step_id','lang']);
$table->foreign('step_id')->references('id')->on('steps')->onDelete('cascade');

Upvotes: 3

Manuel Azar
Manuel Azar

Reputation: 863

Migrations must be created top-down.

First create the migrations for the tables who don't belong to anyone.

Then create the migrations for tables that belong to the previous.

Simplified answer to the table engine problem:

To set the storage engine for a table, set the engine property on the schema builder:

Schema::create('users', function ($table) {
    $table->engine = 'InnoDB';

    $table->increments('id');
});

From Laravel Docs: https://laravel.com/docs/5.2/migrations

Upvotes: 6

nwrman
nwrman

Reputation: 364

In my case the problem was that one of the referenced tables was InnoDB and the other one was MyISAM.

MyISAM doesn't have support for foreign key relations.

So, now both tables are InnoDB. Problem solved.

Upvotes: 4

Related Questions