What is MySQL UNIQUE constraint and how can we apply it to the field of a table?

As the name suggests, MySQL UNIQUE constraint maintains the uniqueness of a column in the table and does not allow inserting the duplicate value. Basically, UNIQUE constraint creates an index such that all the values in the index column must be unique. It is pertinent to mention here that we can have more than one UNIQUE column in a MySQL table.

We can apply UNIQUE constraint by mentioning the ‘UNIQUE’ keyword at the time of defining a column. It can be understood with the help of the following example −

mysql> Create table test3(ID INT UNIQUE, Name Varchar(20));
Query OK, 0 rows affected (0.16 sec)

The above query creates a table named ‘test3’ having a column ‘ID’ with ‘UNIQUE’ constraint on it. We can check it with DESCRIBE statement as follows −

mysql> DESCRIBE test3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | int(11)     | YES  | UNI | NULL    |       |
| Name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

The UNIQUE constraint can be applied to a column of a table with the following query as well −

mysql> Create table test4(ID INT, Name Varchar(20),UNIQUE(ID));
Query OK, 0 rows affected (0.15 sec)

We can check it with DESCRIBE statement as follows −

mysql> DESCRIBE test4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | int(11)     | YES  | UNI | NULL    |       |
| Name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)