Alter ENUM column and add value to that column in Laravel

Question

I have a MySQL database with a table called user_level_attempt. That table has a ENUM type column with ['PROGRESSED', 'STOPPED', 'COMPLETED'] values. I need to write a migration to add another value (let's say 'PASSED') to that column. After adding, it'll look like this, ['PROGRESSED', 'STOPPED', 'COMPLETED', 'PASSED]. How can I do that in Laravel? I tried the following solution but it doesn't seem like a good practice/solution.

 /**
         * Schema table name to migrate
         * @var string
         */
        public $set_schema_table = 'bt_user_level_attempt';


        /**
         * Run the migrations.
         *
         * @return void
         */
        public function up()
        {
            Schema::table($this->set_schema_table, function ($table) {
                $table->dropColumn('status');
            });

            Schema::table($this->set_schema_table, function ($table) {
                $table->enum('status', ['PROGRESS', 'STOPPED', 'COMPLETED', 'PASSED'])->default('PROGRESS')->after('effective_time_spend');
            });
        }

/**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table($this->set_schema_table, function ($table) {
            $table->dropColumn('status');
        });

        Schema::table($this->set_schema_table, function ($table) {
            $table->enum('status', ['PROGRESS', 'STOPPED', 'COMPLETED'])->default('PROGRESS')->after('effective_time_spend');
        });
    }

Thank you.

Nadun Malinda · Accepted Answer · 2019-02-21 12:52:28Z

8

After all, I figure out to find a solution. Thanks to all fellows for enlightening me. :)

/**
     * Schema table name to migrate
     * @var string
     */
    public $set_schema_table = 'bt_user_level_attempt';


    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement("ALTER TABLE ".$this->set_schema_table." MODIFY COLUMN status ENUM('PROGRESS', 'STOPPED', 'COMPLETED', 'PASSED') NOT NULL DEFAULT 'PROGRESS'");
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement("ALTER TABLE ".$this->set_schema_table." MODIFY COLUMN status ENUM('PROGRESS', 'STOPPED', 'COMPLETED') NOT NULL DEFAULT 'PROGRESS'");
    }

edited Feb 21, 2019 at 12:52

answered Feb 21, 2019 at 12:47

Nadun Malinda

3581 gold badge5 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AddWeb Solution Pvt Ltd · Accepted Answer · 2019-02-21 12:30:20Z

5

You should try with DB::statement method:

Use the DB::statement method:

DB::statement("ALTER TABLE ".$this->set_schema_table." CHANGE COLUMN status ENUM('PROGRESS', 'STOPPED', 'COMPLETED','PASSED') NOT NULL DEFAULT 'PROGRESS'");

answered Feb 21, 2019 at 12:30

AddWeb Solution Pvt Ltd

21.8k5 gold badges30 silver badges61 bronze badges

2 Comments

Nadun Malinda Over a year ago

CHANGE is not working. Instead of CHANGE, I tried MODIFY and it worked!

Nadun Malinda Over a year ago

you can do the same to my complete solution as well ;)

Gino Pane · Accepted Answer · 2019-02-21 12:46:44Z

1

Please refer to documentation:

Only the following column types can be "changed": bigInteger, binary, boolean, date, dateTime, dateTimeTz, decimal, integer, json, longText, mediumText, smallInteger, string, text, time, unsignedBigInteger, unsignedInteger and unsignedSmallInteger.

So the ENUM cannot be modified using simple migration syntax. But you can migrate your column using a custom statement:

DB::statement("ALTER TABLE ".$this->set_schema_table." MODIFY COLUMN status ENUM('PROGRESS', 'STOPPED', 'COMPLETED','PASSED') NOT NULL DEFAULT 'PROGRESS'");

answered Feb 21, 2019 at 12:46

Gino Pane

5,0316 gold badges36 silver badges52 bronze badges

Comments

Prathamesh Doke · Accepted Answer · 2019-02-21 13:07:54Z

1

Add this code in your migration file before the schema.

public function __construct()
    {
        \Illuminate\Support\Facades\DB::getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');
    }

answered Feb 21, 2019 at 13:07

Prathamesh Doke

8552 gold badges19 silver badges41 bronze badges

Comments

Jinal Somaiya · Accepted Answer · 2019-02-21 12:17:01Z

-3

try something like :

edit

$table->enum('converted', array('yes','no'))->default('no');

edited Feb 21, 2019 at 12:17

Jinal Somaiya

1,99117 silver badges31 bronze badges

answered Feb 21, 2019 at 12:11

roche

1551 gold badge4 silver badges17 bronze badges

1 Comment

Swayok Over a year ago

This creates/modifies column of VARCHAR type (with CHECK constraint), not ENUM type.

Collectives™ on Stack Overflow

Alter ENUM column and add value to that column in Laravel

5 Answers 5

Comments

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related