how can I create a migration to add a value to an enum in eloquent

Question

I have a table that contains an enum field

CREATE TABLE `user_status` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `values` enum('on', 'off'),
  PRIMARY KEY (`id`),
) ENGINE=InnoDB;

how can I create a migration to add a value to the enum field?

Woodcut

The doctrine docs have something to say about ENUMs and why they are not so good supported: docs.doctrine-project.org/projects/doctrine-orm/en/latest/… – Detrition

Answer 1

Laravel doesn't provide methods to update an enum column. You can delete and recreate the column but you could loose data during the operation and it's not really clean.

In this case, I think that the best choice is to write raw SQL into a migration :

public function up()
{
    DB::statement("ALTER TABLE user_status MODIFY COLUMN ENUM('on','off','unknown')");
}

public function down()
{
    DB::statement("ALTER TABLE user_status MODIFY COLUMN ENUM('on','off')");
}

I could have made a mistake in the SQL syntax, I have never used ENUM, but you can see the idea anyway.

Growth

Be aware that you can only support databases that support the ENUM type with this solution. For example, running tests with SQLite will break. – Aaren

Is it still the "best" way to write raw SQL DDL if you have no idea what database platform the migration will be run on? – Maquette

Any migration name convention for this? – Going

Thanks for this. I put my column name at COLUMN the values part is causing a syntax error you could remove it. – Proclitic

Running "php artisan migrate" gives me "Nothing to migrate". Should I go for manual update in my localhost after updating here? – Astonishing

Just to add an advice in case someone else faces the problem I was facing: in case you try this solution and see an "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;" your problem might be that the column name of the enum column that you're trying to update is one of the many MySQL Keywords and Reserved Words (more here) – Angelita

Answer 2

I did it with MySql:

class ChangeJobTypeEnum extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement("ALTER TABLE _TABLENAME_ CHANGE _COLUMNNAME_ _COLUMNNAME_ ENUM('on', 'off', 'auto')");

    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement("ALTER TABLE _TABLENAME_ CHANGE _COLUMNNAME_ _COLUMNNAME_ ENUM('on', 'off')");

    }
}

Pentateuch

Answer 3

I had a slightly different situation, it was necessary to add new items, change existing and remove old. This is my example.

 <?php

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

class ChangeEnum extends Migration
{   
    public function up()
    {
        Schema::table('table_example', function (Blueprint $table) {
            DB::statement("ALTER TABLE table_example MODIFY status enum('first', 'second', 'third', 'fourth', 'fifth', 'sixth') NOT NULL;");
            DB::statement("UPDATE `field` set `status` = 'fourth' where `status` = 'first';");
            DB::statement("UPDATE `field` set `status` = 'fifth' where `status` = 'second';");
            DB::statement("ALTER TABLE table_example MODIFY status enum('third', 'fourth', 'fifth', 'sixth') NOT NULL;");
        });
    }

    public function down()
    {
        Schema::table('table_example', function (Blueprint $table) {
            DB::statement("ALTER TABLE table_example MODIFY status enum('first', 'second', 'third', 'fourth', 'fifth', 'sixth') NOT NULL;");
            DB::statement("UPDATE `field` set `status` = 'first' where `status` = 'fourth';");
            DB::statement("UPDATE `field` set `status` = 'second' where `status` = 'fifth';");
            DB::statement("ALTER TABLE table_example MODIFY status enum('first', 'second', 'third',) NOT NULL;");
        });
    }
}

By the way, generate row SQL query via JetBrains ide(DataGrip), is like that:

　∧＿∧ 
（｡･ω･｡)つ━☆・*。
⊂　　 ノ 　　　・゜+.
 しーＪ　　　°。+ *´¨)

Heliacal

Answer 4

The second answer works, but in my case CHANGE was throwing an error. So i tried using MODIFY instead and that worked. Thanks guys..

Here is my code:

class ChangeJobTypeEnum extends Migration {

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    DB::statement("ALTER TABLE _TABLENAME_ MODIFY _COLUMNNAME_ ENUM('on', 'off', 'auto')");

}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    DB::statement("ALTER TABLE _TABLENAME_ MODIFY_COLUMNNAME_ ENUM('on', 'off')");

}
}

Camphorate

Answer 5

I say

public function up()
    {
        Schema::create('dt_warehouses', function (Blueprint $table) {
            **$table->enum('isactive', ['Y', 'N'])->default('Y');**
            $table->timestamps();
        });
    }

Baedeker

Answer 6

Since Laravel v10 a new change() modifier is available, so it is no longer needed to use SQL to update enum items.

https://laravel.com/docs/11.x/migrations#modifying-columns

E.g.:

Schema::table('users', function (Blueprint $table) {
    $table->enum('role', ['admin', 'user', 'contributor'])->change();
});

Spier