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!

Does employees migration run before orders migration, right? — Pablo Digiani
Try the show create table employees command and make sure the id on that table is the same collation utf8_unicode_ci and int(10). — user1669496
@PabloDigiani good question. I checked and the employee table was being created after the orders table which was causing the constraint to fail (actually, the employees table wasn't even being created because of the error) . I changed the order in which the migrations fire so that orders is created after employees and it worked. If you want to add the answer below I'll give you the check. Thanks!! — Chris Schmitz

Pablo Digiani Pablo Digiani · Accepted Answer · 2015-12-03T19:34:38

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.

Cannot add foreign key constraint using Laravel 5 migration

An aside

1 Answers