Change unique key together in mysql

mysql unique
yossi picture yossi · Nov 21, 2013 · Viewed 38.7k times · Source

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`)

Answer

juergen d picture juergen d · Nov 21, 2013 
ALTER TABLE your_table 
   DROP INDEX user_id, 
   ADD UNIQUE KEY `user_id` (`user_id`,`account_id`,`pet_id`)

Note: You won't need the backticks around the column names if you're using mariadb on Linux - in fact it will throw an syntax error 1064/(42000)

Related questions

Mysql: Select rows from a table that are not in another

How to select all rows in one table that do not appear on another? Table1: +-----------+----------+------------+ | FirstName | LastName | BirthDate | +-----------+----------+------------+ | Tia | Carrera | 1975-09-18 | | Nikki | Taylor | 1972-03-04 | | Yamila | Diaz | 1972-03-04 | +-----------+----------+------------+ Table2: +…

Read More
Can I use VARCHAR as the PRIMARY KEY?

I have a table for storing coupons/discounts, and I want to use the coupon_code column as the primary key, which is a VARCHAR. My rationale is that, each coupon will have a unique code, and the only commands …

Read More
#1062 - Duplicate entry '' for key 'unique_id' When Trying to add UNIQUE KEY (MySQL)

I've got an error on MySQL while trying to add a UNIQUE KEY. Here's what I'm trying to do. I've got a column called 'unique_id' which is VARCHAR(100). There are no indexes defined on the table. I'm getting this …

Read More