Adding a composite unique key to an existing MySQL table

Introduction

Composite unique keys are very useful for when you want to prevent duplicate data across a number of database fields in a table. For example, suppose we have the following sample table called "user_settings" to store users settings for our application:

Field name	Field type
id	int (PK)
user_id	int (FK)
setting_key	varchar
setting_value	varchar

For each user of the system, we only want to have one setting_key value for each possible setting_key. Therefore a composite unique key would be nice to enforce this: if user_id and setting_key are always unique when combined, this would ensure that no one user has the same setting stored more than once.

This is easy to achieve when creating a new table, but what if the table already exists and contains data?

Checking for existing violations

Before we can add the new unique constraint, we must first check to see of we have any violations and remove them. If we don't do this MySQL will prevent us from adding the new constraint.

The following query will find duplicated pairs for the constraint target fields:

 1 

SELECT user_id, setting_key, COUNT(*) c FROM user_settings GROUP BY user_id, setting_key HAVING c > 1;

If that gives you duplicates back, then you will need to remove them before moving onto the next step.

Adding the new composite unique key

To add the missing constraint:

 1 

ALTER TABLE user_settings ADD UNIQUE KEY `uk_user_settings` (user_id, setting_key);

By convention, I like to prefix unique key names with "uk_" but you can use whatever name you like here. Now to confirm that the key is in place on the table, try:

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 

mysql> SHOW INDEXES FROM user_settings;
+------------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user_settings    |          0 | PRIMARY               |            1 | id          | A         |      133856 |     NULL | NULL   |      | BTREE      |         |               |
| user_settings    |          0 | uk_user_settings      |            1 | user_id     | A         |         200 |     NULL | NULL   | YES  | BTREE      |         |               |
| user_settings    |          0 | uk_user_settings      |            2 | setting_key | A         |         200 |     NULL | NULL   |      | BTREE      |         |               |
| user_settings    |          1 | fk_user_settings_1    |            1 | user_id     | A         |       66928 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.01 sec)