MySQL Add Unique Constraint

Sometimes you may need to make column unique in MySQL. You can do this by adding a UNIQUE column constraint in MySQL. There are a couple of ways to add unique constraint – using CREATE TABLE or ALTER TABLE statements. Here’s how to add unique constraint in MySQL.

MySQL Add Unique Constraint

Here’s how to add unique constraint in MySQL. You can add Unique constraint when you create table, or after you have created table. We will look at both these approaches.

MySQL Add Unique Constraint While Creating Table

Here’s the SQL statement to create UNIQUE constraint when you create table

CREATE TABLE table_name(
    ...,
    column_name data_type UNIQUE,
    ...
)

In the above query, you need to specify UNIQUE keyword for the definition of column which you want to be unique.

Here’s an example to create unique constraint. In the following example, we have added UNIQUE constraint for order_id column

mysql> create table recent_orders
     (
     order_id int UNIQUE,
     amount int
     );

mysql> describe recent_orders;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| order_id | int(11) | YES  | UNI | NULL    |       |
| amount   | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+

Now if you try to insert duplicate values for order_id column you will get an error.

mysql> insert into recent_orders(order_id, amount) values(1, 100),(1,250);
ERROR 1062 (23000): Duplicate entry '1' for key 'order_id'

mysql> insert into recent_orders(order_id, amount) values(1, 100),(2,250);

mysql> select * from recent_orders;
+----------+--------+
| order_id | amount |
+----------+--------+
|        1 |    100 |
|        2 |    250 |
+----------+--------+

Bonus Read : MySQL Rename Column

MySQL Add Unique Constraint To Existing Column

Here’s the SQL statement to create UNIQUE constraint for an existing column, using ALTER TABLE statement.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... );

In the above SQL query, you need to specify a constraint_name for UNIQUE constraint after ADD CONSTRAINT, and list the columns that you want to be unique in ( )

Here’s an example to add Unique constraint to existing column

mysql> alter table recent_orders add constraint unique_amount UNIQUE (amount);

mysql> describe recent_orders;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| order_id | int(11) | YES  | UNI | NULL    |       |
| amount   | int(11) | YES  | UNI | NULL    |       |
+----------+---------+------+-----+---------+-------+

In the above query, we have added unique constraint to amount column.

Bonus Read : How to Duplicate Table in MySQL

MySQL Add Unique Constraint Multiple Columns

Here’s the SQL statement to create UNIQUE constraint to multiple columns.

mysql> create table old_orders
     (
     order_id int,
     amount int,
     CONSTRAINT u_orders UNIQUE (order_id,amount)
     );


mysql> describe old_orders;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| order_id | int(11) | YES  | MUL | NULL    |       |
| amount   | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+


mysql> insert into old_orders(order_id, amount) values(1, 100),(1,100);
ERROR 1062 (23000): Duplicate entry '1-100' for key 'u_orders'

Bonus Read : MySQL Stored Procedure With Parameters

You can also add UNIQUE CONSTRAINT to multiple columns using ALTER TABLE statement.

alter table old_orders add constraint unique_orders UNIQUE (order_id,amount);

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.