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 ?

Are you trying to drop the unique index, or the email column entirely? Also, just a heads-up, your down function also attempts to drop the index, instead of recreating it. — stratedge, Mar 20 '16 at 01:51
In addition to the answers below about the index name, your `down` method should be running the opposite commands to your `up`. — HorusKol, Aug 27 '23 at 22:44

num8er · Answer 1 · 2023-08-27T22:38:07.397

97

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 10.x it's still relevant.

edited Aug 27 '23 at 22:38

answered Dec 28 '16 at 21:58

num8er

18,604
3
43
57

8

This is the better answer in my opinion as it lets Laravel determine the key name using the process it does for creating it. – Fireynis Mar 03 '18 at 22:43
1

Compared to the accepted answer, this is the answer that works for me (on L 5.8). – andromeda Sep 05 '19 at 11:23
1

This answer is better. – Ranga Lakshitha May 18 '20 at 05:23
1

That is a better solution. it works for me in 7.x version too. – sdebarun Jul 21 '20 at 08:49
1

Worked perfectly in 8.x – Han Tran Jun 09 '21 at 09:02

score 64 · Accepted Answer · answered Mar 20 '16 at 02:00

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.

Drop Unique Index Laravel

2 Answers2