Which statement, other than ALTER TABLE statement, can be used to apply UNIQUE constraint to the field of an existing MySQL table?
CREATE UNIQUE INDEX statement can also be used to apply the UNIQUE constraint to the field of an existing MySQL table. The syntax of it is as follows −
CREATE UNIQUE INDEX index_name ON table_name(Column_name);
Example
Suppose we have the following table named ‘Test5’ and we want to add UNIQUE constraint to the column ‘ID’ then it can be done with the help of CREATE UNIQUE INDEX command as follows −
mysql> DESCRIBE TEST5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| Name | varchar(20) | YES| | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)
mysql> CREATE UNIQUE INDEX ID_UNQ ON TEST5(ID);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE test5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(11) | YES | UNI | NULL | |
| Name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)
From the result set of the above query, it has been observed that the column ID is having a UNIQUE constraint.
Published on 16-Feb-2018 12:49:30
