mysql 删除索引 Cannot drop index 'IX_t_billing_setup_CustomerID_WarehouseID': needed in a foreign key constraint

 

--创建表

create table t_billing_setup
(
BillingSetupID int not null auto_increment comment '编号',
CustomerID int not null comment '',
WarehouseID int not null comment '',
CreateTime datetime not null comment '创建时间',
primary key (BillingSetupID)
);

--创建外键约束

alter table t_billing_setup add constraint FK_BillingSetup_Customer foreign key (CustomerID)
references t_customers (CustomerID) on delete restrict on update restrict;

alter table t_billing_setup add constraint FK_BillingSetup_Warehouse foreign key (WarehouseID)
references t_warehouse (WarehouseID) on delete restrict on update restrict;

--------------------------------------------------

--创建一个组合索引
create unique index IX_t_billing_setup_CustomerID_WarehouseID on t_billing_setup
(
CustomerID,
WarehouseID
);

--删除索引
alter table t_billing_setup drop index IX_t_billing_setup_CustomerID_WarehouseID;
--或：drop index IX_t_billing_setup_CustomerID_WarehouseID on t_billing_setup;
--报错：Cannot drop index 'IX_t_billing_setup_CustomerID_WarehouseID': needed in a foreign key constraint
--百度发现：删除‘外键的’索引，必须先删除外键约束（仅会删除外键约束，字段和数据仍然在）

--删除外键约束
alter table t_billing_setup drop foreign key FK_BillingSetup_Warehouse;
alter table t_billing_setup drop foreign key FK_BillingSetup_Customer;

--创建外键唯一索引
create unique index IX_Billing_Setup on t_billing_setup
(
CustomerID,
WarehouseID
);
--添加外键约束
alter table t_billing_setup add constraint FK_BillingSetup_Customer foreign key (CustomerID)
references t_customers (CustomerID) on delete restrict on update restrict;

alter table t_billing_setup add constraint FK_BillingSetup_Warehouse foreign key (WarehouseID)
references t_warehouse (WarehouseID) on delete restrict on update restrict;

--测试重新尝试删除
--alter table t_billing_setup drop index IX_Billing_Setup;
--报错：Cannot drop index 'IX_Billing_Setup': needed in a foreign key constraint

---------------------------------------------------

mysql删除索引方法：

1、利用alter table语句删除，语法为“alter table 数据表名 drop index 要删除的索引名;”；

2、利用drop index语句删除，语法为“drop index 要删除的索引名 on 数据表名;”。

来源：https://www.php.cn/mysql-tutorials-491947.html