Problem with add a foreign key to same table in Laravel - Error : "Cannot add foreign key constraint"

Question

I have a table "users". Some users are linked to other users via the "consultant_id" field.

When I create my database with php artisan migrate, I receive an error PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")

Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('first_name', 255);
            $table->string('last_name', 255);
            $table->string('email', 255)->unique();
            $table->string('password');
            $table->integer('consultant_id')->unsigned()->nullable()->index();
            $table->integer('profile_id')->unsigned()->nullable();
            $table->rememberToken();
            $table->timestamps();
        });

Schema::table('users', function(Blueprint $table) {
            $table->foreign('consultant_id')->references('id')->on('users')
                        ->onDelete('set null')
                        ->onUpdate('cascade');
        });

Can you help me ?

Thank you very much!

Possible duplicate of [Migration: Cannot add foreign key constraint in laravel](https://stackoverflow.com/questions/22615926/migration-cannot-add-foreign-key-constraint-in-laravel) — Aman Kumar, Jul 11 '19 at 07:16
You are adding a foreign key to the same table. Should be `->references('id')->on('consulants')` — Bram Verstraten, Jul 11 '19 at 07:17

score 0 · Accepted Answer · edited Jul 11 '19 at 07:22

0

Your user table's primary key is a big integer, the foreign key field you are trying to use is a normal integer.

Try:

$table->bigInteger('consultant_id')->unsigned()->nullable()->index();

edited Jul 11 '19 at 07:22

Karan

1,146
1
10
24

answered Jul 11 '19 at 07:20

Carl

16
2

score 0 · Answer 2 · answered Jul 11 '19 at 07:25

Try this

 Schema::disableForeignKeyConstraints();

 Schema::table('users', function(Blueprint $table) {
        $table->foreign('consultant_id')->references('id')->on('users')
                    ->onDelete('set null')
                    ->onUpdate('cascade');
 });

Schema::enableForeignKeyConstraints();

score 0 · Answer 3 · answered Jul 11 '19 at 07:35

0

Try to put this code in the down method

Schema::dropForeign(['consultant_id']);

answered Jul 11 '19 at 07:35

draw134

1,053
4
35
84

score -1 · Answer 4 · answered Jul 11 '19 at 07:23

-1

You should try this:

Change following line

$table->integer('consultant_id')->unsigned()->nullable()->index();

TO

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

answered Jul 11 '19 at 07:23

AddWeb Solution Pvt Ltd

21,025
5
26
57

Problem with add a foreign key to same table in Laravel - Error : "Cannot add foreign key constraint"

4 Answers4