How to add un-nullable columns safely to an existing table in Laravel migration?

user_3577922 0
Viewed 1590

I would like to add an un-nullable column to an existing table with some rows in Laravel migration. In SQL, I understand such an action should be performed inside a transaction in the order of

  1. adding a column
  2. initialising the column
  3. making it un-nullable

so as to guarantee

  • the initialisation to be performed without disrupting the DB integrity, and
  • ALTER TABLE not to violate the NOT NULL constraint,

The following is an example PostgreSQL code (assuming users table has a column old_col), referring to an answer:

BEGIN TRANSACTION;
  ALTER TABLE users ADD COLUMN new_col integer;
  UPDATE users SET new_col = old_col + 1;
  ALTER TABLE users ALTER COLUMN new_col SET NOT NULL;
COMMIT;

An ordinary Laravel migration file like this would not work.

public function up()
{
    Schema::table('users', function($table) {
        $table->integer('new_col');  // ->nullable(false) // later?
    });
}

How can one implement a SQL transaction or its equivalent in Laravel migration?

NOTE (edited):
If you want to set up the default value, and if you do not need to (absolutely simultaneously) update the column for the existing rows as a function of some values of each row, then you can simply specify ->default(0) or something like that in the migration file (and avoid all the tricks!). My intention of the question was not to set up the default for the column to add.

laravel migration postgresql sql php
3 Answers

The solution with three queries:

DB::transaction(function () {
    Schema::table('users', function (Blueprint $table) {
        $table->integer('id_cloned')->nullable();
    });

    App\Models\User::query()->update([
        'id_cloned' => DB::raw('id + 1'),
        'updated_at' => DB::raw('now()') // if you want to touch the timestamp
    ]);

    Schema::table('users', function (Blueprint $table) {
        $table->integer('id_cloned')->nullable(false)->change();
    });
});

Alternative solution without DB::raw parts, but will generate separate update query for every record:

DB::transaction(function () {
    Schema::table('users', function (Blueprint $table) {
        $table->integer('id_cloned')->nullable();
    });

    foreach (App\Models\User::all() as $row) {
        $row->id_cloned = $row->id + 1;
        $row->save();
    }

    Schema::table('users', function (Blueprint $table) {
        $table->integer('id_cloned')->nullable(false)->change();
    });
});
user_58147680

You need to set default value to whatever you want:

public function up()
{
    Schema::table('users', function($table) {
        $table->integer('new_col')->default(0); 
    });
}
user_52158060

you can your code in foreach like $methodName = 'item->get'.$method.'()';

class Item {

getFoo();...

getBar();...

}

$methods = ['Foo','Bar'];

foreach($methods as $method){

$methodName = 'item->get'.$method.'()';

echo $methodName;

}

user_103644580
Related