Laravel Migration Foreign key constraint is incorr

Question

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 (`catego  
      ry_id`) references `categories` (`id`) on delete cascade) 

Answer 1

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

Answer 2

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');

Answer 3

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');
    });
}

Answer 4

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');

Answer 5

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.

Answer 6

@JuanBonnett Your question has inspired me to the answer ,i adopted on laravel to automate the process without considering the creation time of the file itself ,According to workflow meals will be created before table (categories) because i have created schema file (meals) before categories. that was my fault.