Setting a foreign key bigInteger to bigIncrements in Laravel 5.4

Question

So I'm trying to set a foreign key in my migrate file for laravel so the user table is simple but I'm trying to use bigIncrements instead of stand increments as such.

 public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->engine = 'InnoDB';
        $table->bigIncrements('id')->unsigned();
        $table->string('user_id')->unique();
        $table->string('avatar');
        $table->string('name');
        $table->string('email')->unique();
        $table->string('password')->nullable();
        $table->rememberToken();
        $table->timestampsTz();
    });
}

And when I do the other table I try to add foreign key to it I get a error saying the the foreign key is incorrectly formed. I'm confused as to how because I'm matching the column types. Here is the other table.

public function up()
{
    Schema::create('social_logins', function (Blueprint $table) {
        $table->engine = 'InnoDB';
        $table->bigIncrements('id');
        $table->bigInteger('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade')->unsigned()->index();
        $table->string('provider', 32);
        $table->string('provider_id');
        $table->string('token')->nullable();
        $table->string('avatar')->nullable();
        $table->timestamps();
    });
}

Try getting rid of the `->unsigned()->index()` at the end of the foreign key statement — ntzm, Feb 24 '17 at 15:36

score 23 · Accepted Answer · answered Feb 24 '17 at 15:37

23

Remove unsigned() from:

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

And the other migration should look like this:

$table->bigInteger('user_id')->unsigned();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
....
$table->index('user_id');

answered Feb 24 '17 at 15:37

Alexey Mezenin

158,981
26
290
279

Thank you so much that did it. – Marcus Lee Feb 25 '17 at 12:08
6

Or simply use `unsignedBigInteger` – Richard Fu Mar 19 '19 at 03:52

score 7 · Answer 2 · answered Oct 25 '19 at 06:51

Reason is primary and foreign references must be of same type.

bigIncrements() wants unsignedBigInteger()

and increments() wants unsignedInteger()

If you are using

$table->bigIncrements('id'); as a primary key in a users table:

then use

$table->unsignedBigInteger('user_id'); as foreign key

And if you are using $table->increments('id'); as a primary key in a users table then use

$table->unsignedInteger('user_id'); as freign key.



$table->increments(); creates just integer and $table->bigIncrements(); creates big integer.

so the reference type must be the same.

Read more https://laravel.com/docs/4.2/schema#adding-columns

score 1 · Answer 3 · answered Feb 24 '17 at 15:36

1

The problem is that bigIncrements returns an unsignedBigInteger. In order to work the Foreign key must be an unsigned big integer with index().

answered Feb 24 '17 at 15:36

ExoticSeagull

1,547
25
46

Setting a foreign key bigInteger to bigIncrements in Laravel 5.4

3 Answers3

Linked