CODEWITHSUNDEEP
laravellaravel-5eloquentnullablelaravel-migrations
user391986
user391986

Reputation: 30946

Laravel Migration Change to Make a Column Nullable

I created a migration with unsigned user_id. How can I edit user_id in a new migration to also make it nullable()?

Schema::create('throttle', function(Blueprint $table)
{
    $table->increments('id');
    // this needs to also be nullable, how should the next migration be?
    $table->integer('user_id')->unsigned();
}

Upvotes: 334

Views: 488229

Answers (12)

Hossein Shafiei
Hossein Shafiei

Reputation: 645

you need first install doctrine/dbal package.

composer require doctrine/dbal

and then use change() method for example:

Schema::table('posts', function (Blueprint $table) {
    $table->unsignedBigInteger('user_id')->nullable()->change();
});

Upvotes: 2

Abdelalim Hassouna
Abdelalim Hassouna

Reputation: 768

I had to use nullable(true)

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

Upvotes: 11

rzb
rzb

Reputation: 2153

Adding to Dmitri Chebotarev's answer, as for Laravel 5+.

After requiring the doctrine/dbal package:

composer require doctrine/dbal

You can then make a migration with nullable columns, like so:

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();
    });
}

To revert the operation, do:

public function down()
{
    /* turn off foreign key checks for a moment */
    DB::statement('SET FOREIGN_KEY_CHECKS = 0');
    /* set null values to 0 first */
    DB::statement('UPDATE `users` SET `user_id` = 0 WHERE `user_id` IS NULL;');
    /* alter table */
    DB::statement('ALTER TABLE `users` MODIFY `user_id` INTEGER UNSIGNED NOT NULL;');
    /* finally turn foreign key checks back on */
    DB::statement('SET FOREIGN_KEY_CHECKS = 1');
}

Upvotes: 30

Dmitri Chebotarev
Dmitri Chebotarev

Reputation: 2547

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;');
}

Upvotes: 236

Fefar Ravi
Fefar Ravi

Reputation: 959

Install Composer Package:

composer require doctrine/dbal

After successfully install composer package we can change data type and change column name using migration command.

Syntax:

php artisan make:migration alter_table_[table_name]_change_[column_name] --table=[table_name]

Example:

php artisan make:migration alter_table_sessions_change_user_id --table=sessions

<?php

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

class AlterTableSessionsChangeUserId extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('sessions', function (Blueprint $table) {
            $table->integer('user_id')->unsigned()->nullable()->change();
        });
    }

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

And run: php artisan migrate

OR

Or table refresh to change column name. not use change method.

Schema::create('throttle', function(Blueprint $table)
{
    $table->increments('id');
    # old code
    $table->integer('user_id')->unsigned();
    # new code
    $table->integer('user_id')->unsigned()->nullable();
}

Note: Below command to clear data from table.

php artisan migrate:refresh --path=/database/migrations/2021_09_31_050851_create_throttle_table.php

Upvotes: 8

MURATSPLAT
MURATSPLAT

Reputation: 4840

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();
        });               

}

Upvotes: 438

Debiprasad
Debiprasad

Reputation: 6183

For Laravel 4.2, Unnawut's answer above is the best one. But if you are using table prefix, then you need to alter your code a little.

function up()
{
    $table_prefix = DB::getTablePrefix();
    DB::statement('ALTER TABLE `' . $table_prefix . '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()
{
    $table_prefix = DB::getTablePrefix();
    DB::statement('ALTER TABLE `' . $table_prefix . 'throttle` MODIFY `user_id` INTEGER UNSIGNED NOT NULL;');
}

Upvotes: 2

Yauheni Prakopchyk
Yauheni Prakopchyk

Reputation: 10942

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.

Upvotes: 58

Sameer
Sameer

Reputation: 1814

Adding to Dmitri Chebotarev Answer,

If you want to alter multiple columns at a time , you can do it like below 

DB::statement('
     ALTER TABLE `events` 
            MODIFY `event_date` DATE NOT NULL,
            MODIFY `event_start_time` TIME NOT NULL,
            MODIFY `event_end_time` TIME NOT NULL;
');

Upvotes: 3

Unnawut
Unnawut

Reputation: 7578

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;');
}

Upvotes: 166

ken
ken

Reputation: 14575

If you happens to change the columns and stumbled on

'Doctrine\DBAL\Driver\PDOMySql\Driver' not found

then just install

composer require doctrine/dbal

Upvotes: 16

Adil
Adil

Reputation: 1038

Try it:

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

Upvotes: 2

Related Questions