Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

murilo's avatar
Level 10

I cant remove / drop unique index key from mysql table

hello , I have this migration -

 Schema::create('my_table', function (Blueprint $table) {
			$table->bigIncrements('id');

            $table->bigInteger('post_id')->unsigned();
            $table->foreign('post_id')->references('id')->on('prop_new')->onDelete('cascade');
		    $table->string('title', 255);
 		   $table->unique(['post_id','title']);
 });

 public function down()
    {
        Schema::table('my_table', function(Blueprint $table){
            $table->dropForeign('my_table_post_id_foreig');

        });
        Schema::dropIfExists('my_table');
    }

    }

Laravel creates a unique key in for post_id and title , called -

my_table_post_id_title_unique

in table - my_table .

If you go to phpmyadmin when you migrate this , you gonna see this key there , on Indexes . I tried to delete this unique key index . but I am not able . it says this message -

#1553 - Cannot drop index 'my_table_post_id_title_unique': needed in a foreign key constraint

I tried as well make those commands in mysql -

ALTER TABLE `my_table` DROP INDEX `my_table_post_id_title_unique`;

or 

DROP INDEX `my_table_post_id_title_unique` ON `my_table`

But I was no success , I cant drop the unique index key .

0 likes
8 replies
sr57's avatar

The cause is written "your index is needed in a foreign key constraint"

in this one : $table->foreign('post_id')->references('id')->on('prop_new')->onDelete('cascade');

If you want to delete the index, you have to delete the constraint first.

Best is to rollback your migration

If for any reason you want to do it in the db "alter table [table] drop constraint [constraint_name];"

murilo's avatar
Level 10

@sr57 I just want to remove the possibility of title to be unique . does have any way to do that ?

sr57's avatar

@murilo

title is not part of the foreign key

you can drop the CONSTRAINT uniq in the db, or always better by rolling back your migration and change table->unique(['post_id','title']); to table->unique(['post_id'']); in the new one

murilo's avatar
Level 10

@sr57 , how coulld I drop the CONSTRAINT uniq in the db? I now how to do in Laravel . but I wold like to learn how to make in mysql .

sr57's avatar

@murilo

I gave you the syntax 3 posts ahead :-)

"alter table [table] drop constraint [constraint_name];"

sr57's avatar

@murilo

Any more information needed before closing this thread?

murilo's avatar
Level 10

@sr57 , I did -

ALTER TABLE `my_table` DROP constraint `my_table_post_id_title_unique`;

and it shewed this message -

Cannot drop index 'my_table_post_id_title_unique': needed in a foreign key constraint
sr57's avatar

@murilo

Lost with your need but this msg is clear, your index enters in a foreign key constraint.

So

  • either it's normal, you can't delete it

  • either you have to delete the foreign constraint before

ALTER TABLE 'my_table' DROP FOREIGN KEY 'my_foreign_key_name' ; 

Please or to participate in this conversation.