35

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?

Laurence
  • 58,936
  • 21
  • 171
  • 212
user391986
  • 29,536
  • 39
  • 126
  • 205
  • The doctrine docs have something to say about ENUMs and why they are not so good supported: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/mysql-enums.html – cweiske Nov 28 '16 at 14:50

5 Answers5

70

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.

Lemon Kazi
  • 3,308
  • 2
  • 37
  • 67
Alexandre Butynski
  • 6,625
  • 2
  • 31
  • 44
  • 5
    Be aware that you can only support databases that support the ENUM type with this solution. For example, running tests with SQLite will break. – Paul Redmond Jan 07 '16 at 17:52
  • 1
    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? – Jason May 11 '18 at 15:05
  • Any migration name convention for this? – Iván E. Sánchez Jul 27 '18 at 18:13
  • Thanks for this. I put my column name at `COLUMN` the `values` part is causing a syntax error you could remove it. – Mike Feb 07 '19 at 10:00
  • Running "php artisan migrate" gives me "Nothing to migrate". Should I go for manual update in my localhost after updating here? – Ahmed Tareque Feb 22 '21 at 09:58
  • 1
    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](https://dev.mysql.com/doc/refman/8.0/en/keywords.html)) – thvs86 Sep 11 '21 at 14:08
6

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

    }
}
mrwaim
  • 1,841
  • 3
  • 20
  • 29
larsemil
  • 880
  • 9
  • 15
2

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:

　∧＿∧ 
（｡･ω･｡)つ━☆・*。
⊂　　 ノ 　　　・゜+.
 しーＪ　　　°。+ *´¨)
dos4dev
  • 429
  • 2
  • 10
  • 26
1

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

}
}
Nick
  • 3,454
  • 6
  • 33
  • 56
Bhola Khawas
  • 11
  • 3
1

I say

public function up()
    {
        Schema::create('dt_warehouses', function (Blueprint $table) {
            **$table->enum('isactive', ['Y', 'N'])->default('Y');**
            $table->timestamps();
        });
    }
нαиdвℓαςк
  • 11
  • 3