Why is creating Foreign Key in Laravel 5.8 failing?

Question

The migration script below was running smoothly in an older version of Laravel but I added it to my fresh Laravel 5.8 and ran the script. I'm getting Error: foreign key was not formed correctly

Evaluation Migration:

public function up() { 
    Schema::create('evaluation', function (Blueprint $table) { 
        $table->increments('id'); 
        $table->integer('user_id')->unsigned()->index(); 
        $table->timestamps();
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    });
}

Users Migration:

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

Does the `users` table exist before you run this? You can't declare a foreign key reference a table before it exists. — Bill Karwin, Mar 04 '19 at 20:22
@BillKarwin ofcourse it does do you want me to share the schema for that? — Iftikhar uddin, Mar 04 '19 at 20:23
That would be helpful, just so we confirm that it is an unsigned int and it's the primary key. — Bill Karwin, Mar 04 '19 at 20:41
You might also like the checklist I contributed to for foreign keys: https://stackoverflow.com/a/4673775/20860 — Bill Karwin, Mar 04 '19 at 20:42
I'm not a laravel expert, but it looks like `users.id` is going to be a `BIGINT` but you're declareing `evaluation.user_id` as an `INT UNSIGNED`? That won't work. They must be exactly the same data type. — Bill Karwin, Mar 05 '19 at 15:21
Aha, I found a [blog](https://laraveldaily.com/foreign-keys-with-migrations-dont-forget-unsigned/) which confirms that the Laravel increments type will be unsigned by default. But you should declare the foreign key column as `bigInteger` to match the size of the primary key it references. — Bill Karwin, Mar 05 '19 at 15:25
Also I'm not sure in Laravel syntax does `bigIncrements` implicitly declare `user.id` as the primary key of that table? The foreign key should reference a column that is the primary key or a unique key. — Bill Karwin, Mar 05 '19 at 15:26
@BillKarwin that's the exact reason I'm getting error. I changed the data type and the error is gone. Thank you so much mate. You can put the solution as an answer. — Iftikhar uddin, Mar 05 '19 at 15:31
@BillKarwin I changed the `user_id` in migration code to `$table->unsignedBigInteger('user_id');` it's working fine now. — Iftikhar uddin, Mar 05 '19 at 15:34

score 20 · Accepted Answer · answered Mar 05 '19 at 15:37

20

As we discussed in the comments above, a foreign key column must be the same data type as the primary key it references.

You declared your user.id primary key as $table->bigIncrements('id') which becomes BIGINT UNSIGNED AUTO_INCREMENT in MySQL syntax.

You must declare the foreign key as $table->unsignedBigInteger('user_id') which will become BIGINT UNSIGNED in MySQL, making it compatible with being a foreign key to the user.id column.

answered Mar 05 '19 at 15:37

Bill Karwin

538,548
86
673
828

2

Aha! `unsignedBigInteger`, not just `bigInteger`! – CJ Dennis Jul 26 '19 at 07:02
Using laravel 5.5. This method still failing. I even separate schema for declaring foreign keys. Still no luck – SMPLYJR Nov 14 '19 at 05:22
@S.Domeng, I suggest you review the checklist [here](https://stackoverflow.com/a/4673775/20860). If it's still failing, try asking a new question and show what you have tried. You're not going to get any useful answers if you only say "_it's failing._" You have to show what you tried AND show the `SHOW CREATE TABLE` output for both tables, and show the exact error message you got. – Bill Karwin Nov 14 '19 at 07:08
Thanks @BillKarwin, but I am in the comment section. I am not sure if explaining things here is the proper way of asking. Moving forward, I figured out the problem... Foreign key constraint from my end Fixed by making the name of the migration reference table file in a plural one. Say create_users_table. My mistake is I have a singular create_user_table name. – SMPLYJR Nov 14 '19 at 08:50
Edited: Not sure if this is the one that throws foreign key constrain in my end. Still, this is the convention from the documentation. However, I did restart my server as well. I use Laragon. You can do it with XAMPP, WAMP and even in Vagrant. – SMPLYJR Nov 14 '19 at 09:01

score 0 · Answer 2 · answered Aug 28 '19 at 02:54

  update your `integer('user_id')` to `bigInteger('user_id')`
public function up() { 
        Schema::create('evaluation', function (Blueprint $table) { 
            $table->increments('id'); 
            $table->bigInteger('user_id')->unsigned()->index(); 
            $table->timestamps();
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        });
    }

Why is creating Foreign Key in Laravel 5.8 failing?

2 Answers2

Linked

Related