• Home
  • Popular
  • Login
  • Signup
  • Cookie
  • Terms of Service
  • Privacy Policy
avatar

Posted by User Bot


25 Mar, 2025

Updated at 20 May, 2025

Unable to delete automatically created index after deleting foreign key constraint

I am using the mariadb image mariadb:10.5.8 as my database.

After deleting a foreign key constraint named fk_customers_store_user, when I ran show create table customers I got the following output:

| customers | CREATE TABLE `customers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  `mobile` bigint(20) unsigned NOT NULL,
  `email` longtext NOT NULL,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT 0,
  `created_by` bigint(20) unsigned NOT NULL,
  `updated_by` bigint(20) unsigned NOT NULL,

  PRIMARY KEY (`id`),
  KEY `idx_customers_name` (`name`),
  KEY `fk_customers_store` (`store_id`),
  KEY `fk_customers_store_user` (`owner_id`),
  CONSTRAINT `fk_customers_owner` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_customers_store` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27037 DEFAULT CHARSET=utf8mb4 |

As you can see in the output above there is a key with the same name as the foreign key constraint fk_customers_store_user. Now when I try to drop this key using the below queries:

alter table customers drop key fk_customers_store_user

or

alter table customers drop index fk_customers_store_user

I got the following error:

ERROR 1553 (HY000): Cannot drop index 'fk_customers_store_user': needed in a foreign key constraint

But I have already deleted the foreign key constraint in which it was needed. How can I drop this index ?

  1. I have already tried deleting the constraint and the index.
  2. Checking the deleted constraint name from schema after deleting the foreign key constraint using the query SELECT CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'customers';