Cannot drop index '***': needed in a foreign key constraint

Question

I have created unique index:

 $table->unique(['owner_id', 'promoter_id']);

and now i tray drop it

$table->dropUnique(['owner_id', 'promoter_id']);

General error: 1553 Cannot drop index 'connections_owner_id_promoter_id_unique': needed in a foreign key constraint (SQL: alter table connections drop index connections_owner_id_promoter_id_unique)

Also, i tried to drop foreign key before

$table->dropForeign('connections_promoter_id_foreign');

but still no results

That's because the name of the foreign key is `connections_owner_id_promoter_id_unique`, as the error tells you — Douwe de Haan, Aug 06 '18 at 13:07
@DouwedeHaan but how i can drop it (unique), i know what error say, but i do not know how i can solve — napalias, Aug 06 '18 at 13:12
`$table->dropForeign('connections_owner_id_promoter_id_unique');` — Douwe de Haan, Aug 06 '18 at 13:13
@DouwedeHaan this does not work too, i still got the error (different), that this key does not exist Syntax error or access violation: 1091 Can't DROP 'connections_owner_id_promoter_id_unique'; check that column/key exists (SQL: alter table `connections` drop foreign key `connections_owner_id_promoter_id_unique`) — napalias, Aug 06 '18 at 13:19
Can you provide us the all the migrations for the table you want to alter and it's related tables? — Douwe de Haan, Aug 06 '18 at 13:20

Adam · Answer 1 · 2022-09-19T09:50:13.057

From the error message, it seems that you created a foreign key and the unique touple in the same run, like this:

$table->foreignId('owner_id')->constrained('owners');
$table->unique(['owner_id', 'promoter_id']);

From mysql docs:

MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created.

Now as you created a tuple index, owner_id already has an index (because its the first of the tuple, note promoter_id does not have an index) and there is no need for MySQL to create a foreign_key index. However, if you would remove the unique key constrain now, you would have a foreignkey without index. This is not allowed, and therefore your error message.

There is no need to remove the foreign key. Just add a normal index on the field before you remove the unique index. Like this:

$table->index('owner_id');
$table->dropUnique(['owner_id', 'promoter_id']);

That's it. The foreign key error won't appear, as you have it indexed.

Thanks.. this explains a lot! – mniess Oct 21 '22 at 10:19 — mniess, Oct 21 '22 at 10:19

score 5 · Accepted Answer · edited Sep 15 '22 at 12:48

5

From Laravel docs on Indexes, you can create the unique index with an assigned name:

Laravel will automatically generate a reasonable index name, but you may pass a second argument to the method to specify the name yourself:

$table->unique('email', 'unique_email');

So just to save you of debugging how laravel constructs the name to the index, you may assign it a name when adding the index e.g:

$table->unique(['owner_id', 'promoter_id'], 'owner_promoter_index');

Then when you drop it, you use the same name:

$table->dropUnique('owner_promoter_index');

edited Sep 15 '22 at 12:48

Adam

25,960
22
158
247

answered Aug 06 '18 at 13:24

Oluwatobi Samuel Omisakin

6,944
3
33
48

Thanks, this seems suppose to work, but for this i needed to add index to older migration – napalias Aug 06 '18 at 13:38
@napalias you don't have to do this for older migrations if they don't give you problem. For me, giving the index a name I chose (when there's a problem) make me confident that the right name is being used, therefore if there comes another problem afterwards I can exclude the index naming from the root cause. – Oluwatobi Samuel Omisakin Aug 06 '18 at 14:16
If you use an array, Laravel will automatically resolve it to correct index name. So `$table->dropUnique(['owner_id', 'promoter_id'])` will remove the correct key. But that was not the problem of OP, problem was conflict with index and foreignkey, see my answer https://stackoverflow.com/a/73730681/2311074 – Adam Sep 15 '22 at 11:59

napalias · Answer 3 · 2018-08-06T14:26:38.120

2

Based on this Drop muli-column unique key without dropping foreign key? i got this solution which also works:

Schema::table('connections', function ($table){
            $table->index('owner_id');
            $table->dropUnique(['owner_id', 'promoter_id']);
        });

edited Aug 06 '18 at 14:26

answered Aug 06 '18 at 13:39

napalias

1,145
1
10
21

score 2 · Answer 4 · answered Sep 17 '21 at 08:18

in your example

$table->unique(['owner_id', 'promoter_id']);

the first column in the array already has a foreign key and a related index.

The First option would be to put first a field that does not have a foreign key already, like

$table->unique(["key", 'owner_id', 'promoter_id']);

But it is not always possible

The second option - you just need to modify the down() function

For example you create your unique index like this

$table->unique(['owner_id', 'promoter_id']);

owner_id is the field that gives you troubles now because it goes the first in the array. Thus, the down function should look like this:

$table->dropForeign(['owner_id']);
$table->dropUnique(['owner_id', 'promoter_id']);
$table->foreign('owner_id')->references('id')->on('owners');

The third option is a bit trickier, your 'down' function will look like this

$table->index('owner_id');
$table->dropUnique(['owner_id', 'promoter_id']);

It will work, but I don't recommend it, because it is not quite a `rollback'

If you start with index name records_owner_id_foreign then you do php artisan migrate and then php artisan migrate:rollback and then you will find your index name records_owner_id_index. It is not the same index name any more

So potentially you can have different index names in different databases, do you like it? I don't.

I think he never wanted to do a rollback. Its normal that you set a unique key and have to remove it in a later migration due to business changes. In this case, I think third option is the right one. — Adam, Sep 15 '22 at 11:56

score 0 · Answer 5 · answered May 26 '22 at 10:04

Try this way:

   Schema::table('table_name', function (Blueprint $table) {
        
        
        //$table->dropIndex('language_title');            
        //$table->dropUnique('language_title');

        // Integrate them
        $table->dropIndex('language_title')->dropUnique('language_title');
    });

score -1 · Answer 6 · answered May 18 '19 at 05:21

-1

Drop the constraint first.

for sqlserver: if you don't know the constraint name,use sp_helpconstraint TABLE_A，the constraint name maybe the same as index. then alter table TABLE_A drop constraint UQ__TABLE_A_XXXXXXXXXX.Then,drop the index.

answered May 18 '19 at 05:21

hatanooh

3,891
1
14
9

score -1 · Answer 7 · answered Dec 31 '22 at 15:41

NO NEED to disable or delete unique index! Just override the UNIQUE index with another one!

Schema::table('users', function (Blueprint $table) {
    $table->unique(['user_id', 'role_id', 'department_id'],
       'user_roles_user_id_role_id_department_id_unique');
});

Cannot drop index '***': needed in a foreign key constraint

7 Answers7