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.

Rishi Raj

Published on 16-Feb-2018 12:49:30

• Related Questions & Answers
• How can we apply UNIQUE constraint to the field of an existing MySQL table?
• How can we apply the PRIMARY KEY constraint to the field of an existing MySQL table?
• Adding unique constraint to ALTER TABLE in MySQL
• How can I see the CREATE TABLE statement of an existing MySQL table?
• What is MySQL UNIQUE constraint and how can we apply it to the field of a table?
• How can we apply a NOT NULL constraint to a column of an existing MySQL table?
• How can we add a FOREIGN KEY constraint to the field of an existing MySQL table?
• What happens when we apply NOT NULL constraint, with ALTER TABLE statement, to a column contains NULL values?
• Which PHP function is used to delete an existing MySQL table?
• How can we get more details about columns of an existing table than return by MySQL SHOW COLUMNS statement?
• Which statement, other than START TRANSACTION, is used for starting a transaction?
• Dropping Unique constraint from MySQL table?
• How to use ALTER TABLE statement for changing the size of a column in MySQL?
• Which PHP function is used to insert data into an existing MySQL table?
• Do we have any other statement in MySQL instead of SHOW COLUMNS to get the list of columns in an existing table?