Drop Unique Index Laravel

Question

I kept getting this while run php artisan migrate

SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'email'; check that column/key exists

While I see that email is exist on my database.

---

My migration script. I was trying to drop the unique constraint.

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AlterGuestsTable3 extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('guests', function(Blueprint $table)
        {
            $table->dropUnique('email');

        });

    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('guests', function(Blueprint $table)
        {

            $table->dropUnique('email');

        });
    }

}

Did I forget to clear any caches ?

Any hints for me ?

Solution 1

When dropping indexes, Laravel will expect that the full name of the index be given.

You can check your database for the full name of the index, but if the key was generated by a previous Laravel migration, its name should conform to a single, simple naming convention.

Here is what the documentation has to say about its naming convention (as of v5.2):

By default, Laravel automatically assigns a reasonable name to the indexes. Simply concatenate the table name, the name of the indexed column, and the index type.

My guess is this is why you are getting an error. There is no email index, but there probably is a guests_email_unique index.

Give this migration a shot:

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AlterGuestsTable3 extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('guests', function(Blueprint $table)
        {
            $table->dropUnique('guests_email_unique');

        });

    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('guests', function(Blueprint $table)
        {
            //Put the index back when the migration is rolled back
            $table->unique('email');

        });
    }

}

I understand it is a little confusing that when creating an index you specify the column names, but when dropping the index later you need to supply the index's full name.

Please note that I've adjusted the down() method as well, so that it reverts dropping the unique index by adding it back.

Solution 2

By official documentation You can see following:

If you pass an array of columns into a method that drops indexes, the conventional index name will be generated based on the table name, columns and key type:

Schema::table('geo', function ($table) {
    $table->dropIndex(['state']); // Drops index 'geo_state_index' 
});

You can drop it just simply using [] around field name:

Schema::table('guests', function(Blueprint $table)
{
    $table->dropUnique(['email']);
});

UPD: By the latest docs for 9.x it's still relevant.

Comments

[code-8]

You saved my life mate :)

[stratedge]

Happy to be of service!

[Fireynis]

This is the better answer in my opinion as it lets Laravel determine the key name using the process it does for creating it.

[andromeda]

Compared to the accepted answer, this is the answer that works for me (on L 5.8).

[Ranga Lakshitha]

This answer is better.

[sdebarun]

That is a better solution. it works for me in 7.x version too.

[Han Tran]

Worked perfectly in 8.x