Migration: Cannot add foreign key constraint

Question

I'm trying to create foreign keys in Laravel however when I migrate my table using artisan i am thrown the following error:

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL
: alter table `priorities` add constraint priorities_user_id_foreign foreign 
key (`user_id`) references `users` (`id`))     

My migration code is as so:

priorities migration file

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

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    //
    Schema::drop('priorities');
}

users migration file

public function up()
{
    //
    Schema::table('users', function($table)
    {
    $table->create();
    $table->increments('id');
    $table->string('email');
    $table->string('first_name');
    $table->string('password');
    $table->string('email_code');
    $table->string('time_created');
    $table->string('ip');
    $table->string('confirmed');
    $table->string('user_role');
    $table->string('salt');
    $table->string('last_login');

    $table->timestamps();
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    //
        Schemea::drop('users');
}

Any ideas as to what I've done wrong, I want to get this right now, as I've got a lot of tables I need to create e.g. Users, Clients, Projects, Tasks, Statuses, Priorities, Types, Teams. Ideally I want to create tables which hold this data with the foreign keys, i..e clients_project and project_tasks etc.

Hope someone can help me to get started.

Answer 1

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

}

Answer 2

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

Answer 3

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.

Answer 4

In my case, the issue was that the main table already had records in it and I was forcing the new column to not be NULL. So adding a ->nullable() to the new column did the trick. In the question's example would be something like this:

$table->integer('user_id')->unsigned()->nullable();

or:

$table->unsignedInteger('user_id')->nullable();

Hope this helps somebody!

Answer 5

In my case the problem was that the auto-generated migration for the users table was setting

...
$table->bigIncrements('id');
...

So I had to change the column type

$table->bigInteger('id');

to make my migration with the foreign key work.

This with laravel 5.8.2

Answer 6

In my case the problem was with migration timing be careful while creating migrations firstly create the child migration than the base migration. Because if you create base migration first which have your foreign key will look for child table and there wont be table which then throw an exception.

Further more:

When you create migration it has a timestamp in the beginning of it. lets say you have created a migration cat so it will look like 2015_08_19_075954_the_cats_time.php and it has this code

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class TheCatsTime extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('cat', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');  
            $table->date('date_of_birth');
            $table->integer('breed_id')->unsigned()->nullable(); 
        });

        Schema::table('cat', function($table) {
        $table->foreign('breed_id')->references('id')->on('breed');
      });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('cat');
    }
}

And after creating the base table you create another migration breed which is child table it has its own creation time and date stamp. The code will look like :

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class BreedTime extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('breed', function (Blueprint $table) {
             $table->increments('id');    
             $table->string('name');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('breed');
    }
}

it seems these both table are correct but when you run php artisan migrate. It will throw an exception because migration will first create the base table in your database because you have created this migration first and our base table has foreign key constraint in it which will look for child table and the child table doesn't exist which is probably an exception..

So:

Create child table migration first.

Create base table migration after child migration is created.

php artisan migrate.

done it will work

Answer 7

In laravel 5.8, the users_table uses bigIncrements('id') data type for the primary key. So that when you want to refer a foreign key constraint your user_id column needs to be unsignedBigInteger('user_id') type.

Answer 8

I had this issue with laravel 5.8 and i fixed this code, as shown here in Laravel documentation, to where ever i am adding a foreign key.

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

then i ran $ php artisan migrate:refresh

Since this syntax is rather verbose, Laravel provides additional, terser methods that use convention to provide a better developer experience. The example above could be written like so:

Schema::table('posts', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained()->onDelete('cascade');
});

Answer 9

In my case I just change the order migrations are executed manually so table users is created first.

In folder database/migrations/ your migration filename have this format: year_month_day_hhmmss_create_XXXX_table.php

Just rename create user file so creation date of your table priorities table is set later than user date (even one second later is enough)

Answer 10

I was having the same issue using Laravel 5.8. After taking a closer look to laravel docs, moreover here Migrations & bigIncrements. The way I solved it is by adding primary keys "$table->bigIncrements('id')" to every single table that is related to the table "users" and its associations, in my case the table "role". Lastly, I had "$table->unsignedBigInteger" for associating roles to users (Many-to-Many), that is, table "role_user".

1. Users table

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

2. Roles Table
    Schema::create('roles', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name')->unique();
        $table->string('display_name')->nullable();
        $table->string('description')->nullable();
        $table->timestamps();
    });

3. Table role_user
Schema::create('role_user', function (Blueprint $table) {
            $table->unsignedBigInteger('user_id');
            $table->unsignedBigInteger('role_id');
            $table->foreign('user_id')->references('id')->on('users')
                ->onUpdate('cascade')->onDelete('cascade');
            $table->foreign('role_id')->references('id')->on('roles')
                ->onUpdate('cascade')->onDelete('cascade');
            $table->primary(['user_id', 'role_id']);
        });

Answer 11

This error occurred for me because - while the table I was trying to create was InnoDB - the foreign table I was trying to relate it to was a MyISAM table!

Answer 12

Using Laravel 5.3 had the same problem.

The solution was to use unsignedInteger instead of integer('name')->unsigned().

So this is what worked

$table->unsignedInt('column_name');
$table->foreign('column_name')->references('id')->on('table_name');

The reason this worked is the fact that when using integer('name')->unsigned the column created in the table had length 11, but when using unsigedInteger('name') the column had length 10.

Length 10 is the length for primary keys when using Laravel so the columns length matched.

Answer 13

We cannot add relations, unless related tables gets created. Laravel run migrations order by date of migration files. So if you want to create a relation with a table that exists in 2nd migration file it fails.

I faced the same problem, so I created one more migration file at last to specify all relations.

Schema::table('properties', function(Blueprint $table) {
        $table->foreign('user')->references('id')->on('users')->onDelete('cascade');
        $table->foreign('area')->references('id')->on('areas')->onDelete('cascade');
        $table->foreign('city')->references('id')->on('cities')->onDelete('cascade');
        $table->foreign('type')->references('id')->on('property_types')->onDelete('cascade');
    });

    Schema::table('areas', function(Blueprint $table) {
        $table->foreign('city_id')->references('id')->on('cities')->onDelete('cascade');
    });

Answer 14

You should write in this way

public function up()
{
    Schema::create('transactions', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->float('amount', 11, 2);
        $table->enum('transaction type', ['debit', 'credit']);
        $table->bigInteger('customer_id')->unsigned();      
        $table->timestamps();                 
    });

    Schema::table('transactions', function($table) {
        $table->foreign('customer_id')
              ->references('id')->on('customers')
              ->onDelete('cascade');
    });     
}

The foreign key field should be unsigned, hope it helps!!

Answer 15

Be aware: when Laravel sets up a table using

$table->increments('id');

which is standard in most migrations, this will set up an unsigned integer field. Therefore when making a foreign reference from another table to this field, ensure that in the referencing table, you set the field to UnsignedInteger and not (what I'd assumed to be an) UnsignedBigInteger field.

For example: in the migration file 2018_12_12_123456_create_users_table.php:

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

Then in the migration file 2018_12_12_18000000_create_permissions_table.php, which sets up the foreign reference back to users:

Schema::create('permissions', function (Blueprint $table){
    $table->increments('id');
    $table->UnsignedInteger('user_id'); // UnsignedInteger = "increments" in users table
    $table->boolean('admin');
    $table->boolean('enabled');
    $table->timestamps();

    // set up relationship
    $table->foreign('user_id')->reference('id')->on('users')->onDelete('cascade');
}

Answer 16

For making addition of foreign key constraint in laravel, the following worked for me:

1. Create the column to be foreign key as follows:

   $table->integer('column_name')->unsigned();

2. Adding the constraint line immediately after (1) i.e.

   $table->integer('column_name')->unsigned();
   $table->foreign('column_name')->references('pk_of_other_table')->on('other_table');

Answer 17

i know thats a old question but make sure if you are working with references the proper supporting engine is defined. set innodb engine for both tables and same data type for the reference columns

$table->engine = 'InnoDB';

Answer 18

One thing i have noticed is that if the tables use different engine than the foreign key constraint does not work.

For example if one table uses:

$table->engine = 'InnoDB';

And the other uses

$table->engine = 'MyISAM';

would generate an error:

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

You can fix this by just adding InnoDB at the end of your table creation like so:

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->unsignedInteger('business_unit_id')->nullable();

        $table->string('name', 100);

        $table->foreign('business_unit_id')
                ->references('id')
                ->on('business_units')
                ->onDelete('cascade');

        $table->timestamps();
        $table->softDeletes();
        $table->engine = 'InnoDB'; # <=== see this line
    });
}

Answer 19

Chiming in here a few years after the original question, using laravel 5.1, I had the same error as my migrations were computer generated with all the same date code. I went through all the proposed solutions, then refactored to find the error source.

In following laracasts, and in reading these posts, I believe the correct answer is similar to Vickies answer, with the exception that you don't need to add a separate schema call. You don't need to set the table to Innodb, I am assuming laravel is now doing that.

The migrations simply need to be timed correctly, which means you will modify the date code up (later) in the filename for tables that you need foreign keys on. Alternatively or in addition, Lower the datecode for tables that don't need foreign keys.

The advantage in modifying the datecode is your migration code will be easier to read and maintain.

So far my code is working by adjusting the time code up to push back migrations that need foreign keys.

However I do have hundreds of tables, so at the very end I have one last table for just foreign keys. Just to get things flowing. I am assuming I will pull those into the correct file and modify the datecode as i test them.

So an example: file 2016_01_18_999999_create_product_options_table. This one needs the products table to be created. Look at the file names.

 public function up()
{
    Schema::create('product_options', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('product_attribute_id')->unsigned()->index();
        $table->integer('product_id')->unsigned()->index();
        $table->string('value', 40)->default('');
        $table->timestamps();
        //$table->foreign('product_id')->references('id')->on('products');
        $table->foreign('product_attribute_id')->references('id')->on('product_attributes');
        $table->foreign('product_id')->references('id')->on('products');


    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::drop('product_options');
}

the products table: this needs to migrate first. 2015_01_18_000000_create_products_table

public function up()
{
    Schema::create('products', function (Blueprint $table) {
        $table->increments('id');

        $table->string('style_number', 64)->default('');
        $table->string('title')->default('');
        $table->text('overview')->nullable();
        $table->text('description')->nullable();


        $table->timestamps();
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::drop('products');
}

And finally at the very end the file that I am temporarily using to resolve issues, which I will refactor as I write tests for the models which I named 9999_99_99_999999_create_foreign_keys.php. These keys are commented as I pulled them out, but you get the point.

    public function up()
    {
//        Schema::table('product_skus', function ($table) {
//            $table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');
//    });

    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
//        Schema::table('product_skus', function ($table)
//        {
//            $table->dropForeign('product_skus_product_id_foreign');
//        });

Answer 20

So simple !!!

if your first create 'priorities' migration file, Laravel first run 'priorities' while 'users' table does not exist.

how it can add relation to a table that does not exist!.

Solution: pull out foreign key codes from 'priorities' table. your migration file should be like this:

and add to a new migration file, here its name is create_prioritiesForeignKey_table and add these codes:

public function up()
{        
    Schema::table('priorities', function (Blueprint $table) {          
        $table->foreign('user_id')
              ->references('id')
              ->on('users');                        
    });
}

Answer 21

make sure your foreing column is over wide rage of foreing key column

I means your foreingkey (in second table) must be same type of your ponter pricipal key (in first table)

your pointer principal key must be add unsigned method, let me show:

on your FIRST migration table:

$table->increments('column_name'); //is INTEGER and UNSIGNED

on your SECOND migration table:

$table->integer('column_forein_name')->unsigned(); //this must be INTEGER and UNSIGNED
$table->foreign('column_forein_name')->references('column_name')->on('first_table_name');

---

ANOTHER EXAMPLE TO SEE DIFFERENCE

on your FIRST migration table:

$table->mediumIncrements('column_name'); //is MEDIUM-INTEGER and UNSIGNED

on your SECOND migration table:

$table->mediumInteger('column_forein_name')->unsigned(); //this must be MEDIUM-INTEGER and UNSIGNED
$table->foreign('column_forein_name')->references('column_name')->on('first_table_name');

SEE MYSQL NUMERIC TYPES TABLE RANGES

Answer 22

If none of the solutions above work for newbies check if both IDs have the same type: both are integer or both are bigInteger, ... You can have something like this:

Main Table (users for example)

$table->bigIncrements('id');

Child Table (priorities for example)

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

This query will failed because users.id is a BIG INTEGER whereas priorities.user_id is an INTEGER.

The right query in this case would be the following:

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

Answer 23

It also may be your the ordering of creation migration. If you firstly create priorities table, and after users table then it will be wrong. Because of first migration looking for users table. So, you have to change the ordering of migration on

app/database/migrations

directory

Answer 24

For me, the issue was an old table was using MyISAM and not InnoDB. This fixed it

    $tables = [
        'table_1',
        'table_2'
    ];

    foreach ($tables as $table) {
        \DB::statement('ALTER TABLE ' . $table . ' ENGINE = InnoDB');
    }

Answer 25

20 April, 2021

In Laravel 8 I have faced this problem. If you don't use nullable() then this error could happen.

$table->bigInteger('user_id')->nullable()->unsigned()->index();
$table->foreign('user_id')->references('id')->on('users')->onUpdate('cascade')->onDelete('set null');

Answer 26

In my case, I was referencing an integer id column on a string user_id column. I changed:

$table->string('user_id')

to:

$table->integer('user_id')->unsigned();

Hope it helps someone!

Answer 27

The gist is that the foreign method uses ALTER_TABLE to make a pre-existing field into a foreign key. So you have to define the table type before you apply the foreign key. However, it doesn't have to be in a separate Schema:: call. You can do both within create, like this:

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

Also note that the type of user_id is set to unsigned to match the foreign key.

Answer 28

You can directly pass boolean parameter in integer column saying that it should be unsigned or not. In laravel 5.4 following code solved my problem.

        $table->integer('user_id', false, true);

Here second parameter false represents that it should not be auto-incrementing and third parameter true represents that it should be unsigned. You can keep foreign key constraint in same migration or separate it. It works on both.

Answer 29

In my case it did not work until I ran the command

composer dump-autoload

that way you can leave the foreign keys inside the create Schema

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

 /**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    //
    Schema::drop('priorities');
}

Answer 30

For me, the table column that my child table referenced wasn't indexed.

Schema::create('schools', function (Blueprint $table) {
    $table->integer('dcid')->index()->unque();
    $table->integer('school_number')->index(); // The important thing is that this is indexed
    $table->string('name');
    $table->string('abbreviation');
    $table->integer('high_grade');
    $table->integer('low_grade');
    $table->timestamps();
    $table->primary('dcid');
});

Schema::create('students', function (Blueprint $table) {
      $table->increments('id');
      $table->integer('dcid')->index()->unique()->nullable();
      $table->unsignedInteger('student_number')->nullable();
      $table->integer('schoolid')->nullable();
      $table->foreign('schoolid')->references('school_number')->on('schools')->onDelete('set null');
      // ...
});

Ignore the terrible naming, it's from another terribly designed system.