Foreign key subsumed by unique constraint in MariaDB/MySQL
I may have stumbled on a solution by trial and error -- correct me if I'm wrong please.

Seems that the FK needs some form of index to function. When creating the unique index, the FK's own index is taken over by the unique one, so that when the unique index is dropped later, the FK would remain "index-less". To prevent that from happening, seems like it is enough to add a normal index before deleting the unique:

ALTER TABLE `actors` ADD index (`user_id`);
alter table `actors` drop index this_subsumes_fk;


At least, with this I don't get an error anymore.