- 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 can we drop UNIQUE constraint from a MySQL table?
For dropping UNIQUE constraint from a MySQL table, first of all, we must have to check the name of the index created by the UNIQUE constraint on the table. As we know that SHOW INDEX statement is used for this purpose. The ‘key_name’ in the result set of SHOW INDEX statement contains the name of the index. Now either with the help of DROP INDEX statement or ALTER TABLE statement, we can drop the UNIQUE constraint. The syntax for both the statements is as follows −
Syntax
DROP INDEX index_name ON table_name; OR ALTER TABLE table_name DROP INDEX index_name;
Example
Suppose we have the table ‘empl’ which have a UNIQUE constraint on column ‘empno’. The index name can be checked as follows −
mysql> Show Index from empl\G *************************** 1. row *************************** Table: empl Non_unique: 0 Key_name: empno Seq_in_index: 1 Column_name: empno Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 1 row in set (0.02 sec)
Now for dropping the UNIQUE constraint, we can write the following query −
mysql> ALTER TABLE empl DROP INDEX empno; Query OK, 0 rows affected (0.26 sec) Records: 0 Duplicates: 0 Warnings: 0
The result set of the query below will show that there is no UNIQUE constraint on column ‘empno’ −
mysql> describe empl; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | empno | int(11) | YES | | NULL | | | F_name | varchar(20) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 2 rows in set (0.04 sec)
Even if we will run the SHOW INDEX from empl query then MySQL will result in an empty set as follows −
mysql> Show index from empl; Empty set (0.00 sec)
The UNIQUE constraint can also be dropped from ‘empl’ table with the help of DROP INDEX statement as follows −
mysql> DROP INDEX empno on empl; Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0
- Related Articles
- Dropping Unique constraint from MySQL table?
- How can we check the indexes created by a UNIQUE constraint on a MySQL table?
- How can we apply UNIQUE constraint to the field of an existing MySQL table?
- What is MySQL UNIQUE constraint and how can we apply it to the field of a table?
- How can we remove NOT NULL constraint from a column of an existing MySQL table?
- How can we remove PRIMARY KEY constraint from a column of an existing MySQL table?
- How can we remove FOREIGN KEY constraint from a column of an existing MySQL table?
- How can we drop a MySQL view from the database?
- Adding unique constraint to ALTER TABLE in MySQL
- How can I drop an existing column from a MySQL table?
- How can we drop a MySQL stored procedure?
- How can we apply a NOT NULL constraint to a column of an existing MySQL table?
- How can we remove a column from MySQL table?
- How can we add a FOREIGN KEY constraint to the field of an existing MySQL table?
- How do I remove a uniqueness constraint from a MySQL table?
