How add unique key to existing table (with non-unique rows)?

You can add unique key to existing table with the help of alter command. The syntax is as follows −

ALTER TABLE yourTableName ADD CONSTRAINT yourConstraintName UNIQUE(yourColumnName1,yourColumnName2,............N);

To understand the above concept, let us create a table with some columns. The query to create a table −

mysql> create table MovieCollectionDemo
   −> (
      −> MovieId int,
      −> MovieDirectorName varchar(200),
      −> NumberOfSongs int unsigned
   −> );
Query OK, 0 rows affected (0.62 sec)

Now you can check the table does not have any unique constraint. The query to check unique constraint is as follows −

mysql> desc MovieCollectionDemo;

The following is the output −

+-------------------+------------------+------+-----+---------+-------+
| Field             | Type             | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| MovieId           | int(11)          | YES  |     | NULL    |       |
| MovieDirectorName | varchar(200)     | YES  |     | NULL    |       |
| NumberOfSongs     | int(10) unsigned | YES  |     | NULL    |       |
+-------------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Now you can add unique key from the above syntax. We are adding unique key on column MovieId. The query is as follows −

mysql> alter table MovieCollectionDemo add constraint uni_moviecollectio unique(MovieId);
Query OK, 0 rows affected (0.46 sec)
Records: 0 Duplicates: 0 Warnings: 0

Let us look at the entire table and the column MovieId to check whether it has unique key or not.

mysql> desc MovieCollectionDemo;

The following is the output −

+-------------------+------------------+------+-----+---------+-------+
| Field             | Type             | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| MovieId           |          int(11) | YES | UNI |     NULL |       |
| MovieDirectorName |     varchar(200) | YES |     |     NULL |       |
| NumberOfSongs     | int(10) unsigned | YES |     |     NULL |       |
+-------------------+------------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

The above output displays the “UNI”, that means the field “MovieId” has Unique Key.

Anvi Jain

Updated on 25-Jun-2020 11:23:55

• Related Questions & Answers
• Remove unique key from MySQL table?
• Primary key Vs Unique key
• Creating Unique Key in MySQL table referring to date?
• Unique Key in RDBMS
• Difference between Primary Key and Unique key
• How to make an existing field Unique in MySQL?
• How can we apply UNIQUE constraint to the field of an existing MySQL table?
• How add a unique key constraint to a column of a table in a database using JDBC API?
• How to ensure that MySQL rows are unique?
• Making an existing field Unique in MySQL?
• Count by unique key in JavaScript
• Python - Unique values count of each Key
• Dropping Unique constraint from MySQL table?
• How to form a composite key to be unique in MySQL?
• JavaScript: How to filter out Non-Unique Values from an Array?