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 ?
SELECT CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'customers';