How to Delete Unique Attributes on MySQL

Many major websites store user data in MySQL databases.

MySQL is noted for its flexibility, and programmers can easily make changes to existing MySQL databases to allow for changes in functionality of their database applications. For example, setting a "Unique" attribute in a MySQL database table prevents the creation of duplicate fields in the table. However, you may want to delete the unique attribute at a later time. For instance, you may have set an address field as unique, but you now have multiple users at the same address. Since "Unique" attributes are index attributes, you delete, or drop, them with the "Drop index" command.

Things You'll Need

• Database editor such as phpMyAdmin (optional)

Instructions

1. Access your MySQL database with the server login, password and commands supplied to you by your administrator or server provider, or by opening it in a database editor such as phpMyAdmin.

2. Type "mysql_query($query);" without the quotation marks into your server command line, or click the name of the database you want to edit in the list of available databases on your database editor. The list is on the left-hand side of the welcome page in phpMyAdmin.

3. Type "mysql> SHOW TABLES" without the quotes, to show the list of available tables, or wait a second for the list to appear after you select the name of your database in your editor.

4. Determine the name of the table for which you need to drop the "Unique" attributes. For example, if you need to allow for duplicate last name entries, look on the list to find a table called "last_names."

5. Type "DROP INDEX `UNIQUE` ON (name of table);" without the quotes, with the exact name of your table in place of the parentheses, and "name of table" into the command prompt before hitting "Enter." Click "Query" in the menu of your editor, and enter or select the name of your table before you type "DROP INDEX 'UNIQUE'" into the query field and click the submission button, which is "Submit query" on phpMyAdmin.

6. Wait a few seconds for confirmation that the attribute has been dropped. Check your application to make sure the change you made allows it to function as necessary.

References

• MySQL: MySQL 5.5 Reference Manual: DROP INDEX Syntax
• FreeWebmasterHelp: PHP/MySQL Tutorial: Part 3 - Inserting Information
• Neal Parikh: MySQL Cheat Sheet