Migration: Cannot add foreign key constraint Migration: Cannot add foreign key constraint laravel laravel

Migration: Cannot add foreign key constraint


Add it in two steps, and it's good to make it unsigned too:

public function up(){    Schema::create('priorities', function($table) {        $table->increments('id', true);        $table->integer('user_id')->unsigned();        $table->string('priority_name');        $table->smallInteger('rank');        $table->text('class');        $table->timestamps('timecreated');    });   Schema::table('priorities', function($table) {       $table->foreign('user_id')->references('id')->on('users');   });}


Question already answered, but hope this might help someone else.

This error occurred for me because I created the migration table with the foreign key in it firstly before the key existed as a primary key in it's original table. Migrations get executed in the order they were created as indicated by the file name generated after running migrate:make. E.g. 2014_05_10_165709_create_student_table.php.

The solution was to rename the file with the foreign key to an earlier time than the file with the primary key as recommended here: http://forumsarchive.laravel.io/viewtopic.php?id=10246

I think I also had to add in $table->engine = 'InnoDB';


Laravel ^5.8

As of Laravel 5.8, migration stubs use the bigIncrements method on ID columns by default. Previously, ID columns were created using the increments method.

This will not affect any existing code in your project; however, be aware that foreign key columns must be of the same type. Therefore, a column created using the increments method can not reference a column created using the bigIncrements method.

Source: Migrations & bigIncrements


Example

Let's imagine you are building a simple role-based application, and you need to references user_id in the PIVOT table "role_user".

2019_05_05_112458_create_users_table.php

// ...public function up(){    Schema::create('users', function (Blueprint $table) {        $table->bigIncrements('id');        $table->string('full_name');        $table->string('email');        $table->timestamps();    });}

2019_05_05_120634_create_role_user_pivot_table.php

// ...public function up(){    Schema::create('role_user', function (Blueprint $table) {        // this line throw QueryException "SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint..."        // $table->integer('user_id')->unsigned()->index();        $table->bigInteger('user_id')->unsigned()->index(); // this is working        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');    });}

As you can see, the commented line will throw a query exception, because, as mentioned in the upgrade notes, foreign key columns must be of the same type, therefore you need to either change the foreing key (in this example it's user_id) to bigInteger in role_user table or change bigIncrements method to increments method in users table and use the commented line in the pivot table, it's up to you.


I hope i was able to clarify this issue to you.