Cannot drop index '*': needed in a foreign key constraint -> But which one?-CodePudding

Context

I am having problems deleting a row from one of our tables.

I use these queries to first drop it's foreign key and afterwards drop the column the key pointed to.

    ALTER TABLE resources drop foreign key fk_res_to_addr;
    ALTER TABLE resources drop column address_id;

Dropping the constraint works just fine. Dropping the column fails with Cannot drop index 'fk_res_to_addr': needed in a foreign key constraint.

What I tried so far

I first tried (and am still trying) to figure out what still relies on this index. I used this query (found in this answer):

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME, 
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    TABLE_SCHEMA = 'some_db' AND
    REFERENCED_TABLE_NAME = 'resources';

But there was nothing there.

I then tried to just disable the checks with:

SET FOREIGN_KEY_CHECKS=0;

And then of course re-enabling them afterwards. This, too, had no effect.

Is there anything else I can do to figure out what depends on this index? Is there something else I am missing?

** Edit - the table definition as requested** This is the table definition as it is right now. As you can see, there is now foreign key for address_id but the index remains.

create table resources
(
id                                     bigint auto_increment primary key,
created                                bigint           not null,
lastModified                           bigint           not null,
uuid                                   varchar(255)     not null,
description                            longtext         null,
internalName                           varchar(80)      null,
publicName                             varchar(80)      not null,
origin                                 varchar(80)      null,
archived                               bigint unsigned  null,
contact_id                             bigint           null,
colorClass                             varchar(80)      null,
address_id                             bigint           null,
url                                    mediumtext       null,
constraint uuid
    unique (uuid),
constraint FK_contact_id
    foreign key (contact_id) references users (id)
)
charset = utf8;

create index fk_res_to_addr on resources (address_id);

create index idx_resources_archived on resources (archived);

create index idx_resources_created on resources (created);

CodePudding user response：

I haven't come across this before but it's so bad I suspect there may be a mysql bug. And it works 'properly' in mariadb.

You should be seeing an error Can't DROP 'fk_res_to_addr'; check that column/key exists as well as the error you are reporting - see https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=c5b0bbc9d6c12f74e00ba8d059a15638

On the create mysql creates an index with the name you have allocated to the fk and allocates it's own name to the fk. The result is the error mentioned above plus https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=c5b0bbc9d6c12f74e00ba8d059a15638.

I suggest you try dropping the key, then the foreign key then the column.

CodePudding user response：

If you will try

SHOW INDEXES FROM database_name.table_name;

it may show you whether fk_res_to_addr' was really dropped.