How can indexes be checked if they exist in a Laravel migration?

Question

Trying to check if a unique index exists on a table when preparing a migration, how can it be achieved?

Schema::table('persons', function (Blueprint $table) {
    if ($table->hasIndex('persons_body_unique')) {
        $table->dropUnique('persons_body_unique');
    }
})

Something that looks like the above. (apparently, hasIndex() doesn't exist)

Peddler

Answer 1

Using "doctrine-dbal" that Laravel uses is better solution:

Schema::table('persons', function (Blueprint $table) {
    $sm = Schema::getConnection()->getDoctrineSchemaManager();
    $indexesFound = $sm->listTableIndexes('persons');

    if(array_key_exists("persons_body_unique", $indexesFound))
        $table->dropUnique("persons_body_unique");
});

Shortening

As mentioned above, this should be the right answer as it goes over Laravel's Doctrine and does not use a native query. – Syringe

I can confirm this works on 5.2, after i installed the doctrine-dbal package from the repo. – Oakum

Be aware that listTableIndexes() will return the name of indexes in lower case. – Menses

Minor add to make copy/paste for other migrations in your future you can use the $table object to get the name. $indexesFound = $sm->listTableIndexes($table->getTable()); – Dispenser

getDoctrineSchemaManager was removed in Laravel 11 – Shalna

In laravel 11 you can use in_array('persons_body_unique', Schema::getIndexListing($table->getTable()) instead – Aylsworth

Why check if the index exists? Just drop it and handle the error if it's not there. – Alveta

Answer 2

The mysql query

SHOW INDEXES FROM persons

will give you back all of the indexes on the table, however it includes additional info other than just the names. In my setup, the column containing the name is called Key_name so lets get a collection of key names

collect(DB::select("SHOW INDEXES FROM persons"))->pluck('Key_name')

And since it's a collection you can use contains so finally we have:

if (collect(DB::select("SHOW INDEXES FROM persons"))->pluck('Key_name')->contains('persons_body_unique')) {
    $table->dropUnique('persons_body_unique');
}

Dall

This shouldn't be the accepted answer as it is not cross-dbms ("portable", not vendor-specific). The answer by @Shortening is much more better as it goes over Laravel's underlaying Doctrine layer and not over a "native query". – Syringe

Answer 3

In the simple form, you can do this

Schema::table('persons', function (Blueprint $table) {
    $index_exists = collect(DB::select("SHOW INDEXES FROM persons"))->pluck('Key_name')->contains('persons_body_unique');
    if ($index_exists) {
        $table->dropUnique("persons_body_unique");
    }
})

Directorate

Answer 4

Laravel 11 has new native schema methods including Schema::getIndexes()

See Laravel documentation here

quoting from Laravel docs.

if (Schema::hasIndex('users', ['email'], 'unique')) {
    // The "users" table exists and has a unique index on the "email" column...
}

Crosslink