- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
- Related Articles
- Remove unique key from MySQL table?
- Creating Unique Key in MySQL table referring to date?
- Primary key Vs Unique key
- How add a unique key constraint to a column of a table in a database using JDBC API?
- How can we apply UNIQUE constraint to the field of an existing MySQL table?
- How to Add a Unique ID Number Column for Duplicate Rows in Excel?
- How to make an existing field Unique in MySQL?
- Unique Key in RDBMS
- How to ensure that MySQL rows are unique?
- Difference between Primary Key and Unique key
- Making an existing field Unique in MySQL?
- Count by unique key in JavaScript
- How to form a composite key to be unique in MySQL?
- How can we add a FOREIGN KEY constraint to the field of an existing MySQL table?
- Python - Unique values count of each Key

Advertisements