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.