Laravel: order of migrations with foreign keys

10 Comments

• 

  Thomas Olsson

  September 27, 2015

  Awesome! Totally saved my day.
  Thank you 😀

• 

  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.

• 

  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)

  • 

    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.

• 

  oldu67

  April 2, 2016

  Thank you:)

• 

  Francisco

  July 17, 2016

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

• 

  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.

  • 

    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.

• 

  Ana Ferreira

  December 10, 2018

  Hi Andrews! I’m new in Laravel and I’m having some trouble with migrations… I have a table named services_service_providers and a other one named solutions, which has a foreign key related to the first one. I’ve migrated de services_service_providers first and only ofter, the solutions table.
  Here are my migrations:

  public function up()
  {
  Schema::create(‘services_service_providers’, function (Blueprint $table) {

  $table->unsignedInteger(‘id’);

  $table->unsignedInteger(‘id_serviceProvider’)->nullable();
  $table->foreign(‘id_serviceProvider’)->references(‘id’)->on(‘service_providers’)->onDelete(‘cascade’);

  $table->unsignedInteger(‘id_service’)->nullable();
  $table->foreign(‘id_service’)->references(‘id’)->on(‘services’)->onDelete(‘cascade’);

  …
  });
  }

  //=================================================================================

  public function up()
  {
  Schema::create(‘solutions’, function (Blueprint $table) {
  $table->increments(‘id’);

  $table->unsignedInteger(‘id_pair’)->nullable();
  $table->decimal(‘value’);
  $table->decimal(‘adherence’);
  $table->integer(‘cost’);
  $table->decimal(‘ratio’);

  $table->timestamps();
  });

  Schema::table(‘solutions’, function (Blueprint $table) {
  $table->foreign(‘id_pair’)->references(‘id’)->on(‘services_service_providers’)->onDelete(‘cascade’);
  });
  }

  //=================================================================================
  //=================================================================================

  And the models:

  class services_service_providers extends Model
  {
  public $table=”services_service_providers”;

  public function solutions(){
  return $this->hasMany(‘App\solutions’,’id’);
  }
  }

  //=================================================================================

  class solutions extends Model
  {
  public function pair(){
  return $this->belongsTo(‘App\services_service_providers’,’id’);
  }
  }

  //=================================================================================
  //=================================================================================

  The error I’m getting:

  In Connection.php line 664:

  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `solutions` add constraint `solutions_id_pair_foreign` foreign key (`id_pair`) references `services_serv
  ice_providers` (`id`) on delete cascade)

  In Connection.php line 458:

  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

  //=================================================================================

  Is there any chance you could help me?
  Is there any more information you need? I’m sorry if it is a silly error…

  Thank you!

  • 

    Andrews Ang

    January 10, 2019

    Have you tried adding this $table->engine='InnoDB'; within your Schema::create(‘solutions’)? I think it used to solve this problem for me. The default engine for older MySQL is MyISAM which does not support foreign constraint.

Comments are closed.