Alter a live table to make a key non-unique

mysql unique alter-table
Alexander Bird picture Alexander Bird · Nov 10, 2010 · Viewed 36k times · Source

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 simply want to make a column no longer unique, which is less permissive in nature so it shouldn't cause any problems.

Answer

Quassnoi picture Quassnoi · Nov 10, 2010

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.

Related questions

Mysql: Select rows from a table that are not in another

How to select all rows in one table that do not appear on another? Table1: +-----------+----------+------------+ | FirstName | LastName | BirthDate | +-----------+----------+------------+ | Tia | Carrera | 1975-09-18 | | Nikki | Taylor | 1972-03-04 | | Yamila | Diaz | 1972-03-04 | +-----------+----------+------------+ Table2: +…

Read More
Can I use VARCHAR as the PRIMARY KEY?

I have a table for storing coupons/discounts, and I want to use the coupon_code column as the primary key, which is a VARCHAR. My rationale is that, each coupon will have a unique code, and the only commands …

Read More
#1062 - Duplicate entry '' for key 'unique_id' When Trying to add UNIQUE KEY (MySQL)

I've got an error on MySQL while trying to add a UNIQUE KEY. Here's what I'm trying to do. I've got a column called 'unique_id' which is VARCHAR(100). There are no indexes defined on the table. I'm getting this …

Read More