How To Change ENUM DataType Without Losing Data Laravel Migration

  • 14-07-2022
  • 915
  • MYSQL
  • Haresh Chauhan

How to change enum datatype without losing data; In this post, you will learn how to change ENUM datatype to text datatype without losing data in your database.

Laravel migration does not allow to change your enum datatype without Doctrine When you trying to change to datatype using laravel migration it will through an error like unknown column type "enum" requested. Any Doctrine type that you use has tobe registered with \Doctrine\DBAL\Types\Type::addType().

So as I've been analyzing the enum in laravel and realized that he is limited varchar by a constrain in my database table, So I can change the constrain without losing data from my database.

I have column name with trainings_status. I want to change this column datatype. So just as I added in this example and then you will be able to change your column datatype.

$types = ['request','running','skip','complete','assign'];

DB::statement("ALTER TABLE trainings DROP CONSTRAINT trainings_status_check");

$result = join( ', ', array_map(function ($value){
    return sprintf("'%s'::character varying", $value);
}, $types));
    
DB::statement("ALTER TABLE trainings ADD CONSTRAINT trainings_status_check CHECK (status::text = ANY (ARRAY[$result]::text[]))");

Migration Example

In this example i have Users table. In this table, i have a status column and this column data is ENUM. I want change to text datatype.

<?php

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

class ColumnChange extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement("ALTER TABLE users DROP CONSTRAINT users_status_check");
        
        $types = ['active','inactive'];

        $result = join( ', ', array_map(function ($value){
            return sprintf("'%s'::character varying", $value);
        }, $types));
        
        DB::statement("ALTER TABLE users ADD CONSTRAINT users_status_check CHECK (status::text = ANY (ARRAY[$result]::text[]))");
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        //
    }
}

We always thanks to you for reading our blogs.


dharmesh-image

Dharmesh Chauhan

(Swapinfoway Founder)

Hello Sir, We are brothers origin from Gujarat India, Fullstack developers working together since 2016. We have lots of skills in web development in different technologies here I mention PHP, Laravel, Javascript, Vuejs, Ajax, API, Payment Gateway Integration, Database, HTML5, CSS3, and Server Administration. So you need our service Please Contact Us

haresh-image

Haresh Chauhan

(Co-Founder)


We Are Also Recommending You :