- Trending Categories
- 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
How do I remove a uniqueness constraint from a MySQL table?
You can use DROP INDEX for this. The syntax is as follows −
alter table yourTablename drop index yourUniqueName;
To understand the above syntax, let us create a table. The query to create a table is as follows −
mysql> create table removeUniquenessConstraint -> ( -> Id int, -> Name varchar(100), -> Age int, -> isGreaterThan18 bool, -> UNIQUE(Id,isGreaterThan18) -> ); Query OK, 0 rows affected (0.69 sec)
Now check the details of table with the help of SHOW CREATE command. The query is as follows −
mysql> show create table removeUniquenessConstraint;
Here is the output −
+----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | removeUniquenessConstraint | CREATE TABLE `removeuniquenessconstraint` (`Id` int(11) DEFAULT NULL,`Name` varchar(100) DEFAULT NULL,`Age` int(11) DEFAULT NULL,`isGreaterThan18` tinyint(1) DEFAULT NULL,UNIQUE KEY `Id` (`Id`,`isGreaterThan18`)) ENGINE =InnoDB DEFAULT CHARSET =utf8 | +----------------------------+--------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
The following is the query to remove a uniqueness constraint from a MySQL table −
mysql> alter table removeUniquenessConstraint drop index `Id`; Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0
Let us check all the details of table once again. The query is as follows −
mysql> show create table removeUniquenessConstraint;
Here is the output −
+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | removeUniquenessConstraint | CREATE TABLE `removeuniquenessconstraint` (`Id` int(11) DEFAULT NULL,`Name` varchar(100) DEFAULT NULL,`Age` int(11) DEFAULT NULL,`isGreaterThan18` tinyint(1) DEFAULT NULL) ENGINE =InnoDB DEFAULT CHARSET =utf8 | +----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Look at the above sample output, there is no uniqueness constraint.
Advertisements