Cannot drop index '*': required -> in foreign key constraint but which one?

Question

Context

I have a problem deleting a row from a table.

I use these queries to first delete its foreign key and then delete the column that the key points to.

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

Removing constraints works well. Dropping the column fails with Cannot drop index 'fk_res_to_addr': required in a foreign key constraint.

What I've tried so far

I first tried (and still try) to find out what is still dependent on that 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 is nothing there.

Then I tried disabling the check:

SET FOREIGN_KEY_CHECKS=0;

Of course, then re-enable them. This also has no effect.

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

** EDIT - table definition as requested** This is the current table definition. As you can see, address_id now has a foreign key, but the index is still there.

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);

Answer 1

If you are willing to try it

SHOW INDEXES FROM database_name.table_name;

It may tell you whether fk_res_to_addr' was actually deleted.

Answer 2

I haven't encountered this before, but it's so bad I suspect there may be a mysql bug. It works "just" in mariadb.

You should see the error Can't DROP 'fk_res_to_addr'; Check if the column/key exists and the error you reported - see https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=c5b0bbc9d6c12f74e00ba8d059a15638

On creation, mysql creates the index using the name you assigned to fk and assigns its own name to fk. The result is the error mentioned above plus https://dbfiddle.uk/?rdbms=mysql_5. 7&fiddle=c5b0bbc9d6c12f74e00ba8d059a15638.

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