Change tables structures with migration without losing data?

Question

I have read several threads about this but still not exactly my problem. So in the users table I have a column called role, which is enum type and has two available values: 1 and 2. I set 2 as the default one. Now I want to change it to 1, for example. I created a new migration, ran php artisan migrate and encounter this error:

[Illuminate\DatabaseQueryException]                                           

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'role' (SQL: alter table `u  
  sers` add `role` enum('1', '2') not null default '1')                                                     
[PDOException]                                                             
  SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'role'

Here is code in my CreateUsersTable migration file:

$table->enum('role', ['1', '2'])->default('2');

And I did the same in the new UpdateUsersTable migration file:

$table->enum('role', ['1', '2'])->default('1');

And by the way I can not use php artisan migrate:refresh because it will delete all my data. Where am I doing wrong?

score 2 · Answer 1 · answered Oct 21 '16 at 12:01

2

$table->enum('role', ['1', '2'])->default('1')->change();

answered Oct 21 '16 at 12:01

aleksejjj

1,715
10
21

tried this and it required me to install doctrine/dbal. – Duong Nguyen Oct 21 '16 at 12:15
but after installing it said that "Unknown database type enum requested, Doctrine\DBAL\Platforms\MySQL57Platform may not support it." – Duong Nguyen Oct 21 '16 at 12:17
Yep, I missed it during answering. There is a problem with `enum` type in laravel. Take a look to this answer http://stackoverflow.com/a/40176304/5130217. Possible it's more correct. – aleksejjj Oct 21 '16 at 12:19
I'm messing things up. Do you know how to uninstall doctrine/dbal? I've tried sudo apt-get remove php-doctrine-dbal but no result. Wrong package name, probably. – Duong Nguyen Oct 21 '16 at 12:27
1

If it was installed with composer you need run `composer remove doctrine/dbal` for remove it – aleksejjj Oct 21 '16 at 12:41

score 1 · Answer 2 · edited May 23 '17 at 12:25

1

<?php

use Illuminate\Support\Facades\DB;
use Illuminate\Database\Migrations\Migration;

class UpdateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement('ALTER TABLE `users` MODIFY `role` DEFAULT 1;');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement('ALTER TABLE `users` MODIFY `role` DEFAULT 2;');
    }
}

Adapted from this answer to another question

edited May 23 '17 at 12:25

Community

1
1

answered Oct 21 '16 at 12:07

Gerard Reches

3,048
3
28
39

@DuongNguyen `ALTER TABLE users CHANGE COLUMN role role ENUM('1', '2') NOT NULL DEFAULT '1' ;` – aleksejjj Oct 21 '16 at 13:05

score 1 · Answer 3 · answered Oct 21 '16 at 15:26

1

In order to do this you have to add ->nullable() or ->default() to every field you add to the migration file.

answered Oct 21 '16 at 15:26

bretanac93

627
8
20

score 0 · Answer 4 · answered Dec 01 '21 at 10:54

create a new migration file

php artisan make:migration add_role_to_users_table --table=users

open the created migration file (database\migrations\2021_12_01_050851-add_role_tables.php) and add below code.

 <?php
 use Illuminate\Database\Migrations\Migration;
 use Illuminate\Database\Schema\Blueprint;
 use Illuminate\Support\Facades\Schema;

 class AddRoleToUsers extends Migration
 {
     /**
      * Run the migrations.
      *
      * @return void
      */
     public function up()
     {
         Schema::table('users', function (Blueprint $table) {
             $table->enum('role', ['1', '2'])->default('1')->comment('1 - admin, 2 - normal'); //added
         });
     }

     /**
      * Reverse the migrations.
      *
      * @return void
      */
     public function down()
     {
         Schema::table('users', function (Blueprint $table) {
             $table->dropColumn('role'); //added
         });
     }
 }
 ?>

Now migration refresh

php artisan migrate:refresh --path=/database/migrations/2021_12_01_050851-add_role_tables.php

Change tables structures with migration without losing data?

4 Answers4