How can we apply UNIQUE constraints on fields of existing MySQL table?

We can apply UNIQUE constraints to columns of existing MySQL tables with the help of ALTER TABLE statement.

Syntax

ALTER TABLE table_name MODIFY colum_name datatype UNIQUE;
                    OR
ALTER TABLE table_name ADD UNIQUE (colum_name);

Example

Suppose we have a table named "Test4" and we want to add a UNIQUE constraint to the "Name" column, then we can use ALTER The TABLE command is used to complete the following -

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)

mysql> ALTER TABLE test4 MODIFY Name Varchar(20) UNIQUE;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0

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

As can be seen from the above result set, MySQL has added a UNIQUE constraint to the field "Name". We can also add UNIQUE constraints using the following query -

mysql> Alter table test4 add UNIQUE(name);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

The above is the detailed content of How can we apply UNIQUE constraints on fields of existing MySQL table?. For more information, please follow other related articles on the PHP Chinese website!