DROP KEY CONSTRAINT IN MYSQL

Step1 : Firstly you have to take backup current table .

create table tb_old like tb_new
insert into tb_new select * from tb_old

Step2:

# rename table tb_old   to  tb_new ; 

Step3: alter table tbl_new  drop  KEY tbl_UNQ_KEY_ID  (drop key from table) 

 # rename table tb_new   to  tb_old ;

 

Other method drop key from table firstly check out key name 

show create table team_person

CREATE TABLE `team_person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `team` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `DOB` int(11) DEFAULT NULL,
  `person` varchar(9) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `email` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_person` (`person`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
 

Drop key :

alter table  team_person drop key email_uk

Add Key :

alter table team_person add constraint id_em_uk unique (id,person)

 Drop index from table :

 alter table team_person drop index  idx_person (index name )

Add index in Table

alter table team_person add index idx_person(person)