How to drop unique constraint from mysql table on a foreign key column

Question

I want to drop just the UNIQUE Constraint from my MySQL table column and keep the Foreign Key Constraint on the column as-is. work_id is the foreign key. Initially, the column was supposed to be unique (one-to-one relationship) which is now not needed. I'm using MySQL Ver 15.1 Distrib 5.5.64-MariaDB.

DESCRIBE requests;
+---------------------+---------------------------------------+------+-----+---------+-------+
| Field               | Type                                  | Null | Key | Default | Extra |
+---------------------+---------------------------------------+------+-----+---------+-------+
| request_id          | char(32)                              | NO   | PRI | NULL    |       |
| owner               | varchar(100)                          | NO   |     | NULL    |       |
| status              | enum('PENDING','ACCEPTED','REJECTED') | YES  |     | NULL    |       |
| work_id             | char(32)                              | NO   | UNI | NULL    |       |
| response_message    | varchar(3000)                         | YES  |     | NULL    |       |
| created_date        | datetime                              | NO   |     | NULL    |       |
| last_modified_date  | datetime                              | NO   |     | NULL    |       |
+---------------------+---------------------------------------+------+-----+---------+-------+

CREATE TABLE `requests` (   
`request_id` char(32) NOT NULL,   
`owner` varchar(100) NOT NULL,   
`status` enum('PENDING','ACCEPTED','REJECTED') DEFAULT NULL,   
`work_id` char(32) NOT NULL,   
`response_message` varchar(3000) DEFAULT NULL,   
`created_date` datetime NOT NULL,   
`last_modified_date` datetime NOT NULL,   
PRIMARY KEY (`request_id`),   
UNIQUE KEY `work_id` (`work_id`),   
CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`work_id`) REFERENCES `work` (`work_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I want to remove UNIQUE Constraint from the work_id. I did some search and executed the following commands.

SHOW INDEX FROM requests;
+-----------------+------------+----------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name       | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| requests        |          0 | PRIMARY        |            1 | request_id        | A         |          16 |     NULL | NULL   |      | BTREE      |         |               |
| requests        |          0 | work_id        |            1 | work_id           | A         |          16 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------+------------+----------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

And then executed

ALTER TABLE requests DROP INDEX work_id;

I get an error

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

Answer 1

So, your problem is you are trying to drop a index which is used in Foreign Key Constraint. So you can not do it directly. Follow below steps:

1. Drop the constraint requests_ibfk_1 which is your foreign key.

alter table requests drop foreign key requests_ibfk_1

2. Then Drop the UNIQUE KEY on column work_id.

alter table requests drop index work_id

3. Again Add Foreign Key on Column work_id.

alter table requests add CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`work_id`) REFERENCES `work` (`work_id`)

DEMO