9

I want to add some new columns in my existing table users in laravel.

I have already googling for that and following those search I have already created migration using the command php artisan make:migration add_columns_to_users.

add_columns_to_users.php

public function up()
{
    Schema::table('users', function($table) {
        $table->string('address');
        $table->string('city');
        $table->string('tribe');
        $table->string('country');
        $table->integer('student_id');
        $table->string('tribe_university_name');
        $table->string('student_program_of_study');
        $table->string('faculty');
        $table->string('level');
    });
}

public function down()
{
    Schema::table('users', function($table) {
        $table->dropColumn('address');
        $table->dropColumn('city');
        $table->dropColumn('tribe');
        $table->dropColumn('country');
        $table->dropColumn('student_id');
        $table->dropColumn('tribe_university_name');
        $table->dropColumn('faculty');
        $table->dropColumn('level');
    });
}

After creation, I run this command php artisan migrate.

But got the same error:

Base table or view already exists: 1050 Table 'users' already exists (SQL: create table users (id int unsigned not null auto_increment primary key, name varchar(255) not null, email varchar(255) not null, password varchar(255) not null, remember_token varchar(100) null, created_at timestamp null, updated_at timestamp null) default character set utf8 collate utf8_unicode_ci)

Full name of user table 2014_10_12_000000_create_users_table.php and the other name is 2019_04_11_074552_add_column_to_users.php

How to solve this?

My main query is How to add new columns in my existing table?

Improve this question
5
  • Can you post the full file names of the initial user table migration and the new one Commented Apr 11, 2019 at 9:38
  • @thisiskelvin.... I have updated my post...please check Commented Apr 11, 2019 at 9:40
  • Whenever you want to add a new column into table best practice is to write a new migration, in this case you have to write migration add_column_to_users --table = users . then add column name in migration and run php artisan migrate. Commented Apr 11, 2019 at 9:40
  • Make sure that your migration class name is addColumnToUser. Commented Apr 11, 2019 at 9:41
  • @ZakariaAcharki...yes it is AddColumnToUsers Commented Apr 11, 2019 at 9:42

6 Answers 6

Reset to default
19

If you check at the error trace:

Base table or view already exists: 1050 Table 'users' already exists

This means that the users table already exists so when you run your migrations it is trying to create a table that is already created in your database.

Note: Don't forget to backup your database first

Delete users table from the database also delete users entries from migrations table.

After, execute the migrate Artisan command:php artisan migrate

Now another your Question is: How to add new columns in my existing table?

You have to create a table using this command:

php artisan make:migration create_users_table

The output you got it like this: Created Migration: 2019_04_12_070152_create_users_table

Your Migration structure is something this: 

public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

Now you want to add new columns in your existing users table

php artisan make:migration add_phone_number_to_users_table --table=users

use the Schema::table() method (as you're accessing an existing table, not creating a new one). And you can add a column like this:

public function up()
{
     Schema::table('users', function (Blueprint $table) {
         $table->string('phonenumber')->after('name'); // use this for field after specific column.
     });
}

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

After, you can run your migrations: php artisan migrate

Your new columns(phonenumber) are now added to your existing users table, which you can view in your database.

If you have still any doubt, see this video

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

Comments

2

You need do little modification in your artisan command

artisan make:migration add_columns_to_users_table

You then need to use the Schema::table() method (as you're accessing an existing table, not creating a new one). And you can add a column like this

  public function up()
  {
     Schema::table('users', function($table) {
          $table->type('column');
      });
   }

add the rollback option:

 public function down()
{
    Schema::table('users', function($table) {
        $table->dropColumn('column');
    });
 }

Then you can run your migrations:

 php artisan migrate
Improve this answer

Comments

1

The problem comes from the php artisan migrate that will try to migrate both files when 2014_10_12_000000_create_users_table.php is already migrated, so IMO two possible solutions here :

  1. Rollback the users table from the DB and rerun the migrate cmd.
  2. Add the migration name inside the migrations table so the cmd will not try to run it for the second time.
Improve this answer

1 Comment

What have you did until now? This is a simple issue that we may face in our daily work, you don't have to waste more time with it... Fix it and go on.
1

Modifying current migration wont work, because it's entry in migration table is already there. So to make any changes in already existing table, you need to create new migration script.

// remember `create` replaced by `table` here  

Schema::table('users', function (Blueprint $table) { 
    // add whichever new columns you want to
});

Follow these steps,

  1. php artisan make:migrate modify_user_table
  2. open modify_user_table file in database/migrations directory

  3. Add new columns as at top I wrote.

  4. Now save the file after adding new columns into new migration file

  5. cmd -> php artisan migrate

EDIT

  1. If there is no user data then Open 2014_10_12_000000_create_users_table.php file and add Schema::dropIfExists('users'); before Schema::create('users'... line.

  2. If there is data then you can take a backup, again follow the above step 1.

Improve this answer

7 Comments

This post doesn't fix the issue of the OP.
@ZakariaAcharki Can you elaborate on why? This is the process we follow, to add new columns in the already migrated table. And its full proof and already tested process.
Because the problem isn't in the process we follow to add new columns, he already followed the right process... So what's the point of telling him something he already did and we can see that from the Posted code.
Got you mate! I will see what can I do. Thanks :)
get the backup of database. empty your database. php artisan migrate. remove migration table from that old backup and run that backup sql.i.e. import. It is must work solution if nothing works.
|
1

Please do the step 1. php artisan migrate:reset Step 2: Go to your database using PHPmyadmin (or similar) and delete all remaining tables

  • including the migration table.

After all please do Step 3 php artisan migrate

Improve this answer

3 Comments

This is really awesome practice. Imagine Facebook one day decides to add new field, like is_developer, and um.. they have to reset all users. Clever.
i have a smarter and simplest solution... delete the db and run php artisan migrate.. hahahah @BenjaminBeganović
@BenjaminBeganović ofc just do php artisan db:wipe and then php artisan migrate --seed to add the new stuff.
0

You probably got the error because you tried to create another Users table, which is already exist on your database. That could be the reason why you find error Base table or view already exists: 1050 Table 'users' already exists.

So, the solution is try to alter you existing Users table, instead to run another syntax to create and override Users table. By create another alter class inside your data migration folder. It used to be inside your modules folder on your Laravel Project (../database/migrations/).

  • After you find the directory, create new alter class. For example, alter_users_table.php.
  • So, if you would like to add another column (e.g.: age column, with type int, nullable) on your Users table, you could code like this on alter_users_table.php:

class AlterUsersAddAge extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('Users', function (Blueprint $table) {
            $table->int('age')->nullable();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('Users', function (Blueprint $table) {
            $table->dropColumn('age');
        });
    }
}
  • After that, try to run php artisan migrate on your terminal.
  • Now you should see another new column age on your Users table.
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.