Cannot add foreign key constraint using Laravel 5 migration

Question

I'm trying to create a migration for an orders table. This table has a foreign key constraint for two tables: employeesand clients.

The schema for the orders table:

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

    $table->integer('client_id')->unsigned();
    $table->foreign('client_id')->references('id')->on('clients');

    $table->integer('employee_id')->unsigned();
    $table->foreign('employee_id')->references('id')->on('employees');

    $table->text('description');
    $table->string('status');
    $table->date('submitted_on');
    $table->date('completed_on');
    $table->timestamps();
});

The schema for the employees table:

Schema::create('employees', function (Blueprint $table) {
    $table->increments('id');
    $table->string('type');
    $table->timestamps();
});

The schema for the clients table:

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

When I run the migration, the constraint is created successfully for the clients table, but for some reason it gives an error when attempting to create the constraint for employees:

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table orders add constraint orders_employee_id_foreign foreign key (employee_id) references employees (id))

I pulled the query it was trying to use out and tried it directly on the database and got the same error:

-- Query:
alter table `orders` add constraint orders_employee_id_foreign foreign key (`employee_id`) references `employees` (`id`)

I'm a bit confused as far as what went wrong. The constraint setup is the same as the clients constraint and that is created successfully:

The syntax I'm using to create each of the constraints match. Is there a reason why it would create one but not the other?

An aside

@PabloDigiani's solution of checking the creation order of the migrations was correct; the orders table was being created before the employees table which caused the error.

The solution was to make sure the employees migration ran before the `orders.

The aside I want to add is how to reorder the migrations in laravel. It's stated here in the docs:

Each migration file name contains a timestamp which allows Laravel to determine the order of the migrations.

When I checked the timestamp order, employees came after orders (just the arbitrary order in which I created the migrations via artisan):

The fix was very simple: rename the migration for employees so that it's timestamp is before orders:

Simple fix. Thanks again!

Answer 1

When migrating a database with Laravel, it is important to check the order in which the tables are created. In this case, employees table should be created before orders table. Otherwise, a foreign key constraint error will be thrown.

Just change the order of the table creation and your migration will run without errors.