CODEWITHSUNDEEP
mysqlunique-constraintunique-key
Jaroslav Beňo
Jaroslav Beňo

Reputation: 67

MYSQL How to safely remove UNIQUE KEY? What to have in mind?

I have a table in database. 

CREATE TABLE `comment_sheets` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `doc_id` mediumint(9) NOT NULL,
  `level` varchar(10) DEFAULT NULL,
  `author` varchar(30) DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL,
  `creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cs` (`doc_id`,`level`,`author`)
) ENGINE=InnoDB AUTO_INCREMENT=3961075 DEFAULT CHARSET=utf8 ;

My UNIQUE KEY cs (doc_id,level,author) is a problem now. I want to remove it, becouse i need duplicate values.

My question is. What should i have in my mind or what shoud I be worry about, when i want delete unique key?

Thanks.

Upvotes: 1

Views: 5018

Answers (3)

Shadow
Shadow

Reputation: 34285

It really depends on how the key is used apart from enforcing uniqueness of data.

  1. Check if the key is used in any foreign key relationships. If yes, you need to drop the foreign key before you can drop the unique one. (Well, mysql won't let you drop the index if the foreign key exits anyway)

  2. Check what queries may make use of the key and how its removal would affect their performance. You may have to add a non-unique key on these fields back.

I this particular case dropping the index is relatively simple task because it is not the primary key, and it is not a fulltext index. The only thing that may take long is the removal of the index data if your table is big (judging from the auto increment value, it is not a small table)

Upvotes: 1

Bahadur Singh Deol
Bahadur Singh Deol

Reputation: 821

To drop unique key

ALTER TABLE table_name
  DROP INDEX index_name;

To drop primary key

    ALTER TABLE table_name
  DROP INDEX `PRIMARY`;

Upvotes: 2

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4211

You need to alter table:

alter table comment_sheets drop INDEX `cs`

Upvotes: 1

Related Questions