Laravel Migration Change to Make a Column Nullable

Laravel 5 now supports changing a column; here's an example from the offical documentation:

Schema::table('users', function($table)
{
    $table->string('name', 50)->nullable()->change();
});

Source: http://laravel.com/docs/5.0/schema#changing-columns

Laravel 4 does not support modifying columns, so you'll need use another technique such as writing a raw SQL command. For example:

// getting Laravel App Instance
$app = app();

// getting laravel main version
$laravelVer = explode('.',$app::VERSION);

switch ($laravelVer[0]) {

    // Laravel 4
    case('4'):

        DB::statement('ALTER TABLE `pro_categories_langs` MODIFY `name` VARCHAR(100) NULL;');
        break;

    // Laravel 5, or Laravel 6
    default:                

        Schema::table('pro_categories_langs', function(Blueprint $t) {
            $t->string('name', 100)->nullable()->change();
        });               

}

Note that this is only possible in Laravel 5+.

First of all you'll need the doctrine/dbal package:

composer require doctrine/dbal

Now in your migration you can do this to make the column nullable:

public function up()
{
    Schema::table('users', function (Blueprint $table) {
        // change() tells the Schema builder that we are altering a table
        $table->integer('user_id')->unsigned()->nullable()->change();
    });
}

You may be wondering how to revert this operation. Sadly this syntax is not supported:

// Sadly does not work :'(
$table->integer('user_id')->unsigned()->change();

This is the correct syntax to revert the migration:

$table->integer('user_id')->unsigned()->nullable(false)->change();

Or, if you prefer, you can write a raw query:

public function down()
{
    /* Make user_id un-nullable */
    DB::statement('UPDATE `users` SET `user_id` = 0 WHERE `user_id` IS NULL;');
    DB::statement('ALTER TABLE `users` MODIFY `user_id` INTEGER UNSIGNED NOT NULL;');
}

I assume that you're trying to edit a column that you have already added data on, so dropping column and adding again as a nullable column is not possible without losing data. We'll alter the existing column.

However, Laravel's schema builder does not support modifying columns other than renaming the column. So you will need to run raw queries to do them, like this:

function up()
{
    DB::statement('ALTER TABLE `throttle` MODIFY `user_id` INTEGER UNSIGNED NULL;');
}

And to make sure you can still rollback your migration, we'll do the down() as well.

function down()
{
    DB::statement('ALTER TABLE `throttle` MODIFY `user_id` INTEGER UNSIGNED NOT NULL;');
}

One note is that since you are converting between nullable and not nullable, you'll need to make sure you clean up data before/after your migration. So do that in your migration script both ways:

function up()
{
    DB::statement('ALTER TABLE `throttle` MODIFY `user_id` INTEGER UNSIGNED NULL;');
    DB::statement('UPDATE `throttle` SET `user_id` = NULL WHERE `user_id` = 0;');
}

function down()
{
    DB::statement('UPDATE `throttle` SET `user_id` = 0 WHERE `user_id` IS NULL;');
    DB::statement('ALTER TABLE `throttle` MODIFY `user_id` INTEGER UNSIGNED NOT NULL;');
}

He're the full migration for Laravel 5:

public function up()
{
    Schema::table('users', function (Blueprint $table) {
        $table->unsignedInteger('user_id')->nullable()->change();
    });
}

public function down()
{
    Schema::table('users', function (Blueprint $table) {
        $table->unsignedInteger('user_id')->nullable(false)->change();
    });
}

The point is, you can remove nullable by passing false as an argument.

Laravel Migration Change to Make a Column Nullable

Related

Recent Posts