How to insert a different value for existing rows than the default in a migration that adds a new column to a table

Viewed 552

I have a migration that updates a table to add a new column:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddStringNormalizationSettingToDataSourcesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('data_sources', function (Blueprint $table) {
            $table->char('do_string_normalization', 1)->default('n')->after('vat_mode');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('data_sources', function (Blueprint $table) {
            $table->dropColumn('do_string_normalization');
        });
    }
}

This works fine, the column is created and all existing rows have the value 'n'. However I would like the default to actually be 'y' so new rows will get created with the value 'y' unless otherwise specified.

How would I specify a different value for the default after the column is created and the existing rows are updated?

I have tried adding a line after the original column insert:

$table->char('do_string_normalization', 1)->default('n');
$table->char('do_string_normalization', 1)->default('y')->change();

But it throws the following exception when running the migration:

Unknown column type "char" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If this error occurs during database introspection then you might have forgotten to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping information.

Googling the string "Unknown column type "char" requested" leads to an issue on the laravel framework's tracker where it is suggested that you can not modify a newly created column in the same migration. I could, I guess, create another migration to change the default. But I would rather not have two migrations just to add a single file if possible. Is it?

Update

After further testing (with multiple migration files and two Schema::table() calls) the issue seems to actually be that doctrine (the package from Symphony used by Laravel to make modifications to existing tables) does not support the char datatype. So this works fine:

public function up()
{
    Schema::table('data_sources', function (Blueprint $table) {
        $table->string('do_string_normalization', 1)->default('n');
    });

    Schema::table('data_sources', function (Blueprint $table) {
        $table->string('do_string_normalization', 1)->default('y')->change();
    });
}

As does this:

public function up()
{
    Schema::table('data_sources', function (Blueprint $table) {
        $table->boolean('do_string_normalization')->default(false);
    });

    Schema::table('data_sources', function (Blueprint $table) {
        $table->boolean('do_string_normalization')->default(true)->change();
    });
}

But this throws up an exception:

public function up()
{
    Schema::table('data_sources', function (Blueprint $table) {
        $table->char('do_string_normalization', 1)->default('n');
    });

    Schema::table('data_sources', function (Blueprint $table) {
        $table->char('do_string_normalization', 1)->default('y')->change();
    });
}
1 Answers

If I understand you correctly, you have an existing table with many rows in it already, and you would like to add a column to this table. For all existing rows you would like the value of the new column to be set to 'n', and for all new rows being added to the table you would like the default to be 'y'.

You can achieve using a raw db statement. This is necessary since there are likely compatibility issues between database types that prevents Doctrine from being able to support changing char type columns.

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('data_sources', function (Blueprint $table) {
            $table->char('do_string_normalization', 1)->default('n')->after('vat_mode');
        });
        
        // Note this may not be compatible with all DBs
        DB::statement('ALTER TABLE data_sources ALTER do_string_normalization SET DEFAULT "y";');
    }
Related