Change unique key together in mysql

Question

I have in my MYSQL table a unique key and i want to add to it.

UNIQUE KEY `user_id` (`user_id`,`account_id`)

and i want to add another

UNIQUE KEY `user_id` (`user_id`,`account_id`,`pet_id`)

juergen d · Accepted Answer · 2023-02-16T06:26:44.213

88

ALTER TABLE your_table 
   DROP INDEX user_id, 
   ADD UNIQUE KEY `user_id` (`user_id`,`account_id`,`pet_id`)

edited Feb 16 '23 at 06:26

answered Nov 21 '13 at 08:49

juergen d

3

Somewhat confusingly, the name of the unique key is in a separate namespace than the column name, so they can be same string but have totally separate meanings. (Stylistically, I prefer to pretend they're in the same namespace to avoid human assumption errors.) In case it's not obvious, you can split these statements apart into two `ALTER TABLE`s. – BaseZen Mar 16 '18 at 18:17

score 2 · Answer 2 · answered Nov 21 '13 at 08:49

2

Drop the first key and then create the new one.

answered Nov 21 '13 at 08:49

Jakob

score 0 · Answer 3 · answered Dec 22 '21 at 06:08

0

I use this code to change field 'uniq_key' which empty

UPDATE your_table  SET `uniq_key`= CONCAT(`user_id`, account_id, pet_id) WHERE  `uniq_key`= ''

answered Dec 22 '21 at 06:08

dewaz

3 Answers3