Home / Snippets / How to Update Column to Nullable in Laravel Migration
How to Update Column to Nullable in Laravel Migration cover

How to Update Column to Nullable in Laravel Migration

33.4K

3 years ago

0 comments

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.

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.

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.
notion avatar

Alaz

Week-end developer currently experimenting with web, mobile, and all things programming.

Topics:

Frontend

Resource

Average

Average

Support Us

If you like our tutorial, support us by being our Patreon or buy us some coffee ☕️

Welcome to PostSrc V3

PostSrc Dark Logo

You have to login to favorite this