Sometimes you will come across a scenario of wanting to update an existing migration column to make it nullable and this is perfectly fine and possible to do in Laravel Migration. In this short snippet, you will learn the steps to make the existing column Nullable using Laravel Migration.
The first step is to require the doctrine/dbal package by using the composer package manager.
Step 1: Require doctrine/dbal package
The first step is to require the doctrine/dbal package by using the composer package manager.
composer require doctrine/dbal
Step 2: Call Schema Facade to Update Columns
Now you need to call the Schema facade and update the columns definition. You can create a new migration for this or you can right away call this function from within the route body. The code to change the column to nullable is as "nullable()->change()".
->nullable()->change();
For this example, we'll be using the route body and we will make the "posts" table "user_id" to be nullable.
<?php
Route::get('/update-column', function () {
\Schema::table('posts', function (Blueprint $table) {
$table->integer('user_id')->unsigned()->nullable()->change();
});
});Now to execute the schema you can visit the "/update-column" URL from your browser and the "user_id" column will be set to nullable.
To revert the column you can set it to not nullable by passing "false" boolean value to the "nullable()"method.
Optional: Revert Column To Be Not Nullable
To revert the column you can set it to not nullable by passing "false" boolean value to the "nullable()"method.
$table->integer('user_id')->unsigned()->nullable(false)->change();Optional: Using Migration
Create the migration by using the migrate command below.
php artisan make:migration make_usre_id_nullable_on_posts_table --table=posts
From the migration file, do update the "up" and "down" method to add the code used to set the value nullable.
<?php
public function up()
{
Schema::table('posts', function (Blueprint $table) {
$table->integer('user_id')->unsigned()->nullable()->change();
});
}
public function down()
{
Schema::table('posts', function (Blueprint $table) {
$table->integer('user_id')->unsigned()->nullable(false)->change();
});
}Optional: Using RAW SQL
You can also use the raw SQL to set the column nullable and below is the code.
<?php
// set the column to nullable
\DB::statement('UPDATE `posts` SET `user_id` = 0 WHERE `user_id` IS NULL;');
// set the column to not nullable
\DB::statement('ALTER TABLE `posts` MODIFY `user_id` INTEGER UNSIGNED NOT NULL;');Important note: Do make sure to have the Doctrine DBAL package installed since internally Laravel uses this package to handle the database changes.
