Alter a live table to make a key non-unique

I saw some other questions related to this, but they were not MySQL.

The database is a live database, so I don't want to delete and recreate the table. I s开发者_开发问答imply want to make a column no longer unique, which is less permissive in nature so it shouldn't cause any problems.

If your column was defined unique using UNIQUE clause, then use:

ALTER TABLE mytable DROP INDEX constraint_name

, or, if your constraint was implicitly named,

ALTER TABLE mytable DROP INDEX column_name

If it was defined unique using PRIMARY KEY clause, use:

ALTER TABLE mytable DROP PRIMARY KEY

Note, however, that if your table is InnoDB, dropping PRIMARY KEY will result in implicit recreation of your table and rebuilding all indexes, which will lock the table and may make it inaccessible for quite a long time.

These are instructions for phpmyadmin app (if you are using phpMyAdmin) ::

In a some cases, the developer (you) may not want to drop it but rather just modify the "uniqueness" to "not-unique".

Steps :

1. Go to the table in context, where you want to make the modification

2. Click on the "Structure" tab (mostly next to Browse)

3. Look for the "+Indexes" link, just under the columns. Yeah... now click it
4. Now you can see all the "Indexes" and you can now click on the "DROP" button or link to modify.

Answer was found here : Source : https://forums.phpfreaks.com/topic/164827-phpmyadmin-how-to-make-not-unique/

Just DROP the unique index. There shouldn't be a problem with the fact that it is a live DB. If it is a really large table, you may block some queries temporarily while the index is removed. But that should only happen if you were adding an index.

ALTER TABLE table_name DROP INDEX index_name;

Although the accepted answer is incorrect (see comments), the suggested workaround is possible. But it is not correct too, at least for a "live table", as asked.

To lower the impact you should create a new index at first, and then delete the old one:

ALTER TABLE mytable ADD INDEX idx_new (column);
ALTER TABLE mytable DROP INDEX idx_old;

This avoids using the table (column) without index at all, which can be quite painful for clients and the MySQL itself.

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast (MySQL Manual).

If the unique key that you want to make non-unique is used by a foreign key constraint, then you'll get an error when dropping it. You will have to recreate it on the same line:

alter table mytable drop KEY myUniqueKey, add key myUniqueKey (myColumn);