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

Published on 10-Jan-2019 07:11:27

Previous Page Print Page