Base Table Or View Already Exists In Laravel Migration

While migrating or rollbacking the database in Laravel, you might get an error like SQLSTATE[42S01]: Base table or view already exists. In this article, we will see how to resolve this error in a correct way. So let’s just jump into it.

Why This Error Occurs?

Let’s suppose, you have run the php artisan migrate command but due to some errors in your code. It won’t be completed successfully but some of the tables already been created into the database.

After correcting the error, you again run the php artisan migrate command and you will get an error which says “SQLSTATE[42S01]: Base table or view already exists“.

Solutions

There are possibly lots of solutions for this error and it depends on the users which to follow according to their needs. Let’s see some of them.

01 Migrate Fresh Command In Laravel

If you are not worried about the data, you have inserted into your database then you can run the following command.

php artisan migrate:fresh

This command will drop all the tables from the database and it will re-create all the tables.

02 Migrate Rollback Command In Laravel

If you have properly created your migrations then this command should work as expected. You should properly add the code into the up() and down() method in your migrations files.

The up() method will execute when you run the php artisan migrate command and the down() method will execute when you run the php artisan migrate:rollback command. So run the following command and see what happens.

php artisan migrate:rollback

This command will roll back the last migrations batch. Then after correcting the errors in your code, you can re-run the php artisan migrate command and you are good to go.

03 Manually Remove The Tables

You can manually drop all the tables in your database. Then fix errors in your code if you have any and then re-run the php artisan migrate command and you are good to go.

04 Use hasTable and hasColumn Methods

Sometimes, it is wise to use methods to avoid errors. We will show you how to use these methods so that you can easily avoid errors while migrating or rollback the database.

Laravel provides the hasColumn('table_name', 'column_name') function to check the column exists in a given table or not.

Same way the hasTable('table_name') method is used to check whether a given table exists or not. Let’s see it by example.

4.1 Check Table Exists Or Not In Laravel Migration

Let’s create a users table only if the users table doesn’t exist in the database.

<?php
 
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
 
class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {

        /*
         * If users table does not exists then create
         * Your newly appended changes won't be added if table already exists
         */
        if (!Schema::hasTable('users')) {
            Schema::create('users', function (Blueprint $table) {
                $table->id();
                $table->uuid('uuid')->unique();
                $table->text('title')->nullable();
                $table->text('first_name');
                $table->text('middle_name')->nullable();
                $table->text('last_name');
                $table->string('email')->unique();
                $table->string('password')->nullable();
                $table->text('gender')->nullable();        
                $table->timestamps();
            });
        }
    }
 
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

4.2 Check Column Exists or Not In Laravel Migration

Let’s suppose, after migrating the database we want remove the 2 columns `middle_name` and `gender`.

<?php
 
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
 
class UpdateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            // The "users" table exists and has an "middle_name" column...
            if (Schema::hasColumn('users', 'middle_name')) {
                $table->dropColumn('middle_name');
            }
 
            if (Schema::hasColumn('users', 'gender')) {
                $table->dropColumn('gender');
            }
        });
    }
 
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            if (!Schema::hasColumn('users', 'middle_name')) {
                $table->text('middle_name')->nullable()->after('first_name');
            }
 
            if (!Schema::hasColumn('users', 'gender')) {
                $table->text('gender')->nullable()->after('password');
            }
        });
    }
}

In the above example, we are checking that if a certain column exists then and then drop the column else don’t do anything.

Additionally, read our guide:

That’s it from our end. We hope this article helped you to solve SQLSTATE[42S01]: Base table or view already exists issue in the Laravel.

Please let us know in the comments if everything worked as expected, your issues, or any questions. If you think this article saved your time & money, please do comment, share, like & subscribe. Thank you for reading this post 🙂 Keep Smiling! Happy Coding!