Laravel: order of migrations with foreign keys

Honestly I didn’t know that SQLite is so forgiving until I had to migrate to MySQL in production recently.

Planning the database schema ahead of implementation was a habit, and it didn’t occur to me that the order of migrations with foreign keys was so important. SQLite didn’t produce any error, but when running Laravel migration in MySQL,  it produced an error like this for tables with foreign keys:

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL : alter table `profiles` add constraint profiles_company_id_foreign foreign key (`company_id`) references `companies` (`id`))

I was so puzzled because the schema looked correct and it ran without error with SQLite. So it took me awhile to realise that only 2 tables were affected, and coincidentally that 2 tables were the first 2 to be created of all migrations.

The order of the old migrations was like this:

  1. 2014_01_01_00001_create_profiles_table.php
    • Foreign key company_id references to id of table companies
  2. 2014_01_01_00002_create_contacts_table.php
    • Foreign key contacttype_id references to id of table contacttypes
  3. 2014_01_01_00003_create_companies_table.php
  4. 2014_01_01_00004_create_contacttypes_table.php

That first 2 tables referenced to some tables that didn’t exist at the point of migration, that’s why MySQL had an error trying to add a constraint.

I had to change the order of the migrations for that 2 tables to be created without error.

Modified order of migrations:

  1. 2014_01_01_00001_create_companies_table.php
  2. 2014_01_01_00002_create_contacttypes_table.php
  3.  2014_01_01_00003_create_profiles_table.php
    • Foreign key company_id references to id of table companies
  4.  2014_01_01_00004_create_contacts_table.php
    • Foreign key contacttype_id references to id of table contacttypes

Moral of the story: Tables with foreign keys should be created after the tables they reference to have been created.

I hope this will help someone else in the future.


8 Comments

  • Thomas Olsson

    September 27, 2015

    Awesome! Totally saved my day.
    Thank you 😀

    Reply
  • Brock Alexander Ellis

    December 30, 2015

    You’re a saint. Thank’s so much for blogging about this. Definitely helped n00bs to see step-by-step explanations like this.

    Reply
  • Andrés Gris

    March 7, 2016

    You can separate the schema creation from the relations definition, after all you can CREATE a table and then ALTER it, just put the stuff like Schema::create($tableName, function($table) { $table->foo(‘bar’)}) in the UP function of one migration, and, in the UP function of another migration (file), the relations like Schema::table($tableName, function($table) { $table->foreign(fkey)->references(‘id’)->on(‘productos’); });
    Also note the format of the dropForeign in the docs (https://laravel.com/docs/5.2/migrations#foreign-key-constraints).
    This way I found to be practical because the order doesnt matter at all (except for the relations related migrations, which has to be the last migration on the list)

    Reply
    • Andrews Ang

      March 8, 2016

      Your way works if we want to separate relationships migration from creations.
      But for my case I needed the relationship along with the table creation. It’s neater for my case.
      Thanks for the suggestion though, it may help other people who may prefer to have relations in another migration file.

      Reply
  • oldu67

    April 2, 2016

    Thank you:)

    Reply
  • Francisco

    July 17, 2016

    Just logged in to say thank you, you just saved my day. Francisco.

    Reply
  • Sven

    September 15, 2016

    Hey,

    first of all, I must admit, that I ran into the same problem and that’s why I am here.
    And now comes the big “BUT”: The reason we (myself included) run into this problem is that we use migrations wrong. My former workflow was as follows:
    1) create tables using a migration
    2) develop
    3) add columns/constraints/etc to the initial migration
    4) wipe the development database, run the migrations again, go back to 2)

    This is not the way migrations are meant to be used.
    In step 3) you should create a new migration that alters the initial table to add the new columns or constraints. Since you did it after other changes you might have made in between, the constraints will always point to valid/existing tables/columns.

    So, the ‘moral of the story’ shouldn’t be to create tables in a specific order but to use migrations the way they were meant to be used so that they gradually change the database and any intermediate state is valid.

    Reply
    • Andrews Ang

      October 6, 2016

      Hi Sven, I think you misunderstood my “moral of the story”. First, you are right in every way about the usage of migrations. However, during the initial stage when you plan out the foundation tables, have you considered the sequence of those tables? For example, if there’s a table “profiles” that points to a foreign key user_id in table “users”, which table should be created first? In that case, you have not created the tables yet, so you can’t just make incremental changes to it. In that case, the file name of those migration files are important. Because I made a mistake of renaming them in reverse orders and only to realise that it will cause an error in MySQL but not SQLite.

      Reply

Leave a Reply