Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
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
Dropping Unique constraint from MySQL table?
First, let us create a table with the UNIQUE constraint. It suggests that we cannot add duplicate values.
Creating a table.
mysql> create table UniqueConstraintDemo -> ( -> Name varchar(200) unique -> ); Query OK, 0 rows affected (1.05 sec)
Now, we can check whether the table has UNIQUE constraint or not with the help of DESC command. The query is as follows.
mysql> DESC UniqueConstraintDemo;
The following is the output.
+-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | Name | varchar(200) | YES | UNI | NULL | | +-------+--------------+------+-----+---------+-------+ 1 row in set (0.00 sec)
Now, we have unique constraint on column ‘Name’. Therefore, we cannot add duplicate values. If we try to insert same value twice then we will get an error. The query to insert records are as follows.
mysql> insert into UniqueConstraintDemo values('John');
Query OK, 1 row affected (0.54 sec)
mysql> insert into UniqueConstraintDemo values('John');
ERROR 1062 (23000): Duplicate entry 'John' for key 'Name'
Look at the Error 1062 above. It suggests that we tried adding duplicate values to a column with UNIQUE constraint.
Let us now see the syntax for dropping the UNIQUE constraint.
DROP index yourColumnName on yourTableName;
Apply the above syntax to remove the unique constraint.
mysql> DROP index Name on UniqueConstraintDemo; Query OK, 0 rows affected (0.39 sec) Records: 0 Duplicates: 0 Warnings: 0
We can now check whether UNIQUE constraint has been removed or not with the help of DESC command. The query is as follows −
mysql> DESC UniqueConstraintDemo;
The following is the output that displaying we have successfully removed UNIQUE constraint.
+-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | Name | varchar(200) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 1 row in set (0.00 sec)
313 Views
