CODEWITHSUNDEEP
laravellaravel-4foreign-keysmigrationeloquent
user0129e021939232
user0129e021939232

Reputation: 6355

Migration: Cannot add foreign key constraint

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.

Upvotes: 257

Views: 343708

Answers (30)

abdalrahman salama
abdalrahman salama

Reputation: 11

I think you need to write nullable() before constrained('tabel name')

Example:

$table-> foreignId (' parent_id')
           -> nullable ( ) 
            -> constrained ('categories','id')
            -> nullOnDelete ()

Upvotes: 0

kaamrul
kaamrul

Reputation: 53

You can do this:

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

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

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

In my case, I did this DB::statement('SET FOREIGN_KEY_CHECKS=0;'); to turn off foreign key check and 1 to turn on foreign key check. It's worked! This solution is better for the specified problem.

Upvotes: 0

hassanrazadev
hassanrazadev

Reputation: 664

All the above solution are somehow correct but none of them worked for me.

The thing that was creating problem for me is Database Table Engine. Engine type of already added table was MyISAM and new migration was setting engine type = 'InnoDB'

I just set Engine Type of already added tables to InnoDB and that's it!

enter image description here

Upvotes: 0

Martinho Mussamba
Martinho Mussamba

Reputation: 467

Shortcut solution:

//Inside Schema::create

$table->foreign('user_id')->bigInteger('user_id')->unsigned()->index();

//Inside Schema::table

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

//Working on your code.

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

  Schema::table('administrador', function($table) {
            $table->foreign('users_id')->references('id')->on('users')
            ->onUpdate('cascade')
            ->onDelete('cascade');
;
        });

}

Upvotes: 1

Tim Bogdanov
Tim Bogdanov

Reputation: 240

In the automatically created create_users_table migration change $table->id(); to $table->increments('id');. Did the trick for me, hope this helps!

Upvotes: 2

Muhammad
Muhammad

Reputation: 387

Please first check the data type of the column. if first table id type is bigint then while adding foreign key check the type should be bigint in other table.

Upvotes: 0

Mr. Perfectionist
Mr. Perfectionist

Reputation: 2746

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

Upvotes: 5

shanecp
shanecp

Reputation: 851

Another cause could be the DB engine types. Laravel by default uses MyISAM engine. So if you need to enforce foreign key reference checks, you should use InnoDB as your engine.

This setting is in your config/database.php file. Change the engine to 'engine' => 'InnoDB'

Also if you're trying to do this to an existing table, change the engine by using

DB::statement("ALTER TABLE `{$tableName}` ENGINE = 'InnoDB'");

Upvotes: 2

Heterocigoto
Heterocigoto

Reputation: 328

(Learning english, sorry) I try in my project with "foreignId" and works. In your code is just delete the column user_id and add the foreignId on the reference:

 public function up()
{

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

IMPORTANTE: Create first the tables without foreign keys on this case the "users" table

Upvotes: 3

Slycreator
Slycreator

Reputation: 1230

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

Upvotes: 19

Dazzle
Dazzle

Reputation: 3083

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

Upvotes: 2

BlockCode
BlockCode

Reputation: 31

Some times this error may come because of sequence of migrations.

Like Users and Order are two tables

Order table have foriegn key of users (During migration if Order table migrate first then it will cause the problem because there is no users to match foreign key)

Solution: Just Put your Order Update table under the users for update

Example: In my case Education and University tables Education Table

public function up()
{
    Schema::create('doc_education', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('uni_id')->unsigned()->nullable();
        $table->timestamps();
    });
}

In the University 

    Schema::create('doc_universties', function (Blueprint $table) {
        $table->increments('id');
        $table->string('uni_name');
        $table->string('location')->nullable();
        $table->timestamps();

        //
    });



Schema::table('doc_education', function(Blueprint $table) {
        $table->foreign('uni_id')->references('id')
        ->on('doc_universties')->onDelete('cascade');
    });

Upvotes: 1

Mahesh Yadav
Mahesh Yadav

Reputation: 2684

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!!

Upvotes: 6

Dhara Talaviya
Dhara Talaviya

Reputation: 631

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.

Upvotes: 20

erlandmuchasaj
erlandmuchasaj

Reputation: 232

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

Upvotes: 3

chebaby
chebaby

Reputation: 7730

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.

Upvotes: 90

Capfer
Capfer

Reputation: 901

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

Upvotes: 13

Grant
Grant

Reputation: 1994

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.

Upvotes: 1

Daniele
Daniele

Reputation: 1063

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

Upvotes: 29

Vicky
Vicky

Reputation: 982

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

Upvotes: 24

bnoeafk
bnoeafk

Reputation: 539

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

Upvotes: 5

Turan Zamanlı
Turan Zamanlı

Reputation: 3926

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

Upvotes: 2

pavan kumar
pavan kumar

Reputation: 401

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

Upvotes: 6

Vladimir Salguero
Vladimir Salguero

Reputation: 5947

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

Upvotes: 2

Waiyl Karim
Waiyl Karim

Reputation: 2950

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

Upvotes: 3

Mohit Satish Pawar
Mohit Satish Pawar

Reputation: 61

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.

Upvotes: 1

Rub&#233;n Ru&#237;z
Rub&#233;n Ru&#237;z

Reputation: 609

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

Upvotes: 2

user6602005
user6602005

Reputation:

I think: the reference key must be "index". for example:(down)

public function up()
{
    Schema::create('clicks', function (Blueprint $table) {
        $table->increments('id');
        $table->string('viewer_id');
        $table->integer('link_id')->index()->unsigned();
        $table->string('time');
        $table->timestamps();
    });

    Schema::table('clicks', function($table) {
        $table->foreign('link_id')->references('id')->on('links')->onDelete('cascade');
    });


}

good luck.

Upvotes: 0

ldt
ldt

Reputation: 131

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)

Upvotes: 13

Menasheh
Menasheh

Reputation: 3708

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.

Upvotes: 1

Related Questions