Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Change unique key together in mysql

Tags:

mysql

unique

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`) 
like image 671
yossi Avatar asked Nov 21 '13 08:11

yossi


People also ask

How do I change the unique key in MySQL?

Sometimes we want to add a unique key to the column of an existing table; then, this statement is used to add the unique key for that column. Following are the syntax of the ALTER TABLE statement to add a unique key: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column_list);

Can we modify unique key?

To modify a unique constraint In the Object Explorer, right-click the table containing the unique constraint and select Design. On the Table Designer menu, click Indexes/Keys.... In the Indexes/Keys dialog box, under Selected Primary/Unique Key or Index, select the constraint you wish to edit.

Can I define multiple unique key in a MySQL table?

We can define multiple Unique keys on a table where one or more columns combine to make a Unique key. According to ANSI, we can use multiple NULL values but in the SQL server, we can add only one NULL value.

How do I change the unique key on a table?

The syntax for creating a unique constraint using an ALTER TABLE statement in SQL Server is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.


1 Answers

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)

like image 194
juergen d Avatar answered Sep 23 '22 16:09

juergen d