Honestly I didn’t know that SQLite is so forgiving until I had to migrate to MySQL in production recently.
SQLite is so forgiving that migrating to MySQL becomes a big headache. Moral of the story: always test on the DB you're going to use in prod
— Andrews Ang (@kongnir) February 27, 2015
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:
- 2014_01_01_00001_create_profiles_table.php
- Foreign key company_id references to id of table companies
- 2014_01_01_00002_create_contacts_table.php
- Foreign key contacttype_id references to id of table contacttypes
- 2014_01_01_00003_create_companies_table.php
- 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:
- 2014_01_01_00001_create_companies_table.php
- 2014_01_01_00002_create_contacttypes_table.php
- 2014_01_01_00003_create_profiles_table.php
- Foreign key company_id references to id of table companies
- 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.
Thomas Olsson
September 27, 2015Awesome! Totally saved my day.
Thank you 😀
Brock Alexander Ellis
December 30, 2015You’re a saint. Thank’s so much for blogging about this. Definitely helped n00bs to see step-by-step explanations like this.
Andrés Gris
March 7, 2016You 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)
Andrews Ang
March 8, 2016Your 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.
oldu67
April 2, 2016Thank you:)
Francisco
July 17, 2016Just logged in to say thank you, you just saved my day. Francisco.
Sven
September 15, 2016Hey,
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.
Andrews Ang
October 6, 2016Hi 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.