How to make an existing field Unique in MySQL?

To make an existing field unique in MySQL, we can use the ALTER command and set UNIQUE constraint for the field. Let us see an example. First, we will create a table.

mysql> create table AddingUnique
   -> (
   -> Id int,
   -> name varchar(100)
   -> );
Query OK, 0 rows affected (0.44 sec)

Syntax to add UNIQUE to an existing field.

alter table yourTableName add UNIQUE(yourColumnName);

Applying the above syntax in order to add UNIQUE to column ‘name’.

mysql> alter table AddingUnique add UNIQUE(name);
Query OK, 0 rows affected (0.60 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now we cannot insert duplicate records into the table, since we have set the field to be unique. If we try to add duplicate records then it raises an error.

mysql> alter table AddingUnique add UNIQUE(name);
Query OK, 0 rows affected (0.60 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into AddingUnique values(1,'John');
Query OK, 1 row affected (0.15 sec)

mysql> insert into AddingUnique values(1,'John');
ERROR 1062 (23000): Duplicate entry 'John' for key 'name'

mysql> insert into AddingUnique values(2,'Carol');
Query OK, 1 row affected (0.18 sec)

mysql> insert into AddingUnique values(3,'John');
ERROR 1062 (23000): Duplicate entry 'John' for key 'name'

mysql> insert into AddingUnique values(4,'Smith');
Query OK, 1 row affected (0.18 sec)

To display all records.

mysql> select *from AddingUnique;

The following is the output.

+------+-------+
| Id   | name  |
+------+-------+
|    1 | John  |
|    2 | Carol |
|    4 | Smith |
+------+-------+
3 rows in set (0.00 sec)

Arjun Thakur

e

Updated on: 30-Jul-2019

6K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started