3

I need to update an existing Laravel application by migrating and seeding the database.

I have a table like the following:

items

  • id
  • name

And I want to edit the database to look like:

items

  • id
  • name
  • type_id (new column)

types (new table)

  • id
  • name

With type_id being a not-null foreign key to the types table.

The types table will be seeded with a Laravel seeder in a following operation. This seeder will be called after each application update and truncate/reinsert some "static" data that only change with updates of the application. It is configured in a way like Laravel : Migrations & Seeding for production data for the local/production differences

Everything runs fine when starting from scratch on local database. But on the production database, there are already many records. As it is a not-null key, the migration fails when pushing to this database (actually, a copy of that database for testing)

As a migrating solution, I'd like to add the first type record to every existing item, but I cannot set the foreign key during the migration, as the items table is empty at this stage, and I cannot leave it empty until the seeding, as the migration don't pass.

There are some things I think to:

  • Disable the foreign key checks on the database during the whole update, but I'd prefer a solution that allows me to simply push my repository to the server and start my automated deployment script (which essentially call artisan migrate followed by artisan db:seed).
  • Call the seeder from the migration just after the creation of the types table, but I'm not sure that is a good practice

What should I do ?

Edit: Here's my working migration code using @lukasgeiter answer:

public function up()
{
    DB::statement('SET FOREIGN_KEY_CHECKS = 0');

    Schema::table('items', function(Blueprint $table)
    {
        $table->integer('type_id')->unsigned()->index()->after('name');
    });

    DB::update('update items set type_id = 1');

    Schema::table('items', function(Blueprint $table)
    {
        $table->foreign('type_id')->references('id')->on('types');

        // Here I do other stuff to this table
    });

    DB::statement('SET FOREIGN_KEY_CHECKS = 1');
}
Community
  • 1
  • 1
Kaktus
  • 153
  • 4
  • 23

1 Answers1

6

You can just disable foreign key checks inside the migration by using DB::statement and SQL.

DB::statement('SET FOREIGN_KEY_CHECKS = 0');
// add column
DB::statement('SET FOREIGN_KEY_CHECKS = 1');
lukasgeiter
  • 147,337
  • 26
  • 332
  • 270
  • But when do I run my Seeder ? If I reenable the foreign keys after adding the column without doing anything else it will fail (null still not permitted) – Kaktus Jan 17 '15 at 23:05
  • I thought you'd seed `type_id` with a valid value? – lukasgeiter Jan 17 '15 at 23:07
  • Yes it's right I will set the right foreign keys but the types table is still empty at this time, it will only be seeded at the next step of the deployment (artisan db:seed) – Kaktus Jan 17 '15 at 23:13
  • I still think this should work though. First you turn key checks off, create the column and turn them on again. Then (when seeding) you seed the `types` table and afterwards update the `type_id` in `items`. Am I wrong? – lukasgeiter Jan 17 '15 at 23:26
  • No it was me who was wrong =| . I thought dealing with the database that way would cause an error but it actually works as expected. I'll add my working code in the question. Thanks ! – Kaktus Jan 17 '15 at 23:55