Laravel migration: “Foreign key constraint is incorrectly formed")

Question

My migration fails when I try to execute php artisan migrate or php artisan migrate:refresh with this error:

errno: 150 "Foreign key constraint is incorrectly formed"

Code

Users Table (migrated successfully)

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->integer('id',)->primary();
        $table->string('name', 255);
        $table->string('surname', 255);
        $table->string('email', 255)->unique();
        $table->string('password', 255);
        $table->string('profile_image', 255);
        $table->string('profile_cover', 255);
        $table->rememberToken();
        $table->char('token', 255)->unique()->nullable()->default(null);
        $table->enum('role', ['user', 'driver', ',merchant', 'storemanager', 'storeoperator', 'company', 'employee']);
        $table->dateTime('created');
        $table->dateTime('updated');
    });
}

Address Table (fails with error)

public function up()
{
    Schema::create('address', function (Blueprint $table) {
        $table->integer('id')->primary();
        $table->string('address1');
        $table->string('address2');
        $table->string('city');
        $table->integer('country')->unsigned();
        $table->decimal('latitude');
        $table->decimal('longitude');
        $table->string('instructions');
        $table->integer('default');
        $table->integer('user')->unsigned();
        $table->dateTime('created');
        $table->dateTime('updated');
        $table->foreign('country')->references('id')->on('country')->onUpdate('cascade');
        $table->foreign('user')->references('id')->on('users')->onUpdate('cascade');

    });
}

Answer 1

The reason it failed is as @aynber mentioned. The key's data type must match.

It is also important to follow Laravel best practice for naming primary key and foreign key. The primary key should be 'id' and foreign key should be table name (singular) underscore id. For example if the table name is users then the foreign key for table users should be 'user_id'.

Here is the example for table addresses (plural)

    Schema::create('addresses', function (Blueprint $table) {
        $table->id();
        ...

        $table->foreignId('user_id')->constrained();
    });

You don't have to provide the table name or the primary key of another table because Laravel will figure that out from foreign key (user_id translates to table users with primary key id). Later on, when you create an eloquent model you can leave several information from your model.

    class Address {

        public function user()
        {
            return $this->belongsTo(User::class);
        }
    }

Here you don't have to specify $table property and also you don't have to provide the foreign key or primary key in belongsTo method.

Answer 2

One of the reasons to work with a framework such as Laravel is syntactic sugar. By following their conventions, you write less code and have to think about fewer of the "nuts and bolts" of your application.

You are doing the opposite of this, and are already seeing the consequences. You will experience so much more pain once you try setting up models and their relationships. Instead you should be using a migration that looks like this:

<?php

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

class CreateAddressesTable extends Migration
{
    public function up()
    {
        // table names are plural of the object name
        Schema::create('addresses', function (Blueprint $table) {
            // automatically create an incrementing bigint column called 'id'
            $table->id();
            // name foreign ID columns correctly to save yourself trouble later
            $table->foreignId('country_id')->constrained();
            $table->foreignId('user_id')->constrained();
            // specify lengths for your varchar columns
            $table->string('address1', 100);
            $table->string('address2', 100);
            $table->string('city', 100);
            $table->decimal('latitude');
            $table->decimal('longitude');
            $table->string('instructions', 100);
            $table->integer('default');
            // why would you define your own timestamp columns with non-standard names?
            $table->timestamps();
        });
    }
}

And the users table; you'd want to update its migration similarly.

<?php

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

class CreateUsersTable extends Migration
{
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            // DO NOT use an enum column; create another table called roles instead
            $table->foreignId('role_id')->constrained();
            $table->string('name', 100);
            $table->string('surname', 100);
            $table->string('email', 100)->unique();
            $table->string('password', 255);
            $table->string('profile_image', 255);
            $table->string('profile_cover', 255);
            $table->rememberToken();
            // 
            $table->char('token', 255)->unique()->nullable()->default(null);
            $table->timestamps();
        });
    }

Answer 3

you can change id of table users to:

$table->id();

and change in ADress table user to user_id and try to change foreign id like this:

$table->foreignId('user_id')->constrained()->onDelete('cascade');

Answer 4

In you address migration instead of:

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

Put:

$table->integer('user');

Both the FK and the reference key should be the same type.

Answer 5

The data types on each table must match exactly for the keyed columns. On users, id is a signed integer, on address, user is an unsigned integer. I would suggest using unsigned integers for the id since it should never go negative, and gives you a higher upper limit.