Laravel Migration: Add Index - How to Check if it Already Exists?

If you want to add an index to the column in Laravel migrations and you're not sure if that index already exists, how to make sure that no error happens when running migrations?

You might have noticed that it has no built-in way to check if an index exists.

It is a problem because if you try to add an index that exists, you will get an error "duplicate key name" on migrations:

A solution to this is using a simple script that would load all indexes from the database.

Installing Doctrine DBAL

Since this is using Doctrine, you'll have to install the doctrine/dbal package:

composer require doctrine/dbal

Otherwise, you might encounter an error:

Class "Doctrine\DBAL\Driver\AbstractMySQLDriver" not found

Code snippet

Migration

Schema::table('TABLE_NAME', static function (Blueprint $table) {
    $schemaManager = Schema::getConnection()->getDoctrineSchemaManager();
    $indexesFound  = $schemaManager->listTableIndexes('TABLE_NAME');
 
    if (! array_key_exists('INDEX_TO_CREATE', $indexesFound)) {
        $table->index('FIELD_TO_INDEX', 'INDEX_TO_CREATE');
    }
});

Example 1: Add an Index

For example, if you want to create an index on the email field of the users table, you would use the following code:

Migration

Schema::table('users', static function (Blueprint $table) {
    $schemaManager = Schema::getConnection()->getDoctrineSchemaManager();
    $indexesFound  = $schemaManager->listTableIndexes('users');
 
    if (! array_key_exists('users_email_index', $indexesFound)) {
        $table->index('email', 'users_email_index');
    }
});

Example 2: Drop an Index

And if you want to drop the same email index, you would use the following code:

Migration

Schema::table('users', static function (Blueprint $table) {
    $schemaManager = Schema::getConnection()->getDoctrineSchemaManager();
    $indexesFound  = $schemaManager->listTableIndexes('users');
 
    if (array_key_exists('users_email_index', $indexesFound)) {
        $table->dropIndex('users_email_index');
    }
});

That's it, now you have a list of indexes available to you, and you can check if an index exists before you try to add it.

Login or register to comment or ask questions
avatar
Ivan Mykhavko

Hello. I sometime use this scheme:

class extends Migration {

    private string $tableName = 'product_prices';

    private string $indexNameUnique = 'p_r_product_id_price_type_id_unique';

    private array $groupColumns = ['product_id', 'price_type_id'];

    public function up()
    {
        $sm = Schema::getConnection()->getDoctrineSchemaManager();
        $indexes = $sm->listTableIndexes($this->tableName);

        DatabaseHelper::removeDuplicates($this->tableName, $this->groupColumns);

        Schema::table($this->tableName, function (Blueprint $table) use ($indexes) {
            // indexes
            if (array_key_exists($this->indexNameUnique, $indexes)) {
                $table->dropIndex($this->indexNameUnique);
            }

            $table->unique($this->groupColumns, $this->indexNameUnique);

            // columns
            $table->unsignedTinyInteger('price_type_id')->change();
            $table->unsignedMediumInteger('product_id')->change();

            $table->dropColumn('created_at');
        });
    }
}
👍 1

Like our articles?

Become a Premium Member for $129/year or $29/month
What else you will get:
  • 59 courses (1056 lessons, total 44 h 09 min)
  • 78 long-form tutorials (one new every week)
  • access to project repositories
  • access to private Discord

Recent Premium Tutorials