Drop Unique Constraint in MySQL

March 28, 2008 by 12 Comments

If you would like to drop a unique constraint from your table in MySQL, there is a very simple way to do it. Thanks to how to drop unique constraint, you can do so as well. The trick is to know that you have to drop an index, instead of a unique constraint. Consider the following example:

create table mytable (
accountNumber tinyint unsigned not null,
authCode tinyint unsigned not null,
authorized enum ('NO', 'YES') not null default 'NO',
constraint uk_mytable unique(accountNumber, authCode));

In this example, I am setting a name (of my choosing) for the constraint. The reason is if I want to do anything with the constraint, I have an easily recognizable name for it. Now if you want to drop a unique constraint, you do this (tested in MySQL 5.0 and confirmed it works):

alter table mytable drop index uk_mytable;

Remember, do not do the following (or any variation of it) because it will give you SQL error:

alter table mytable drop unique constraint uk_mytable;

To recap, if you want to drop a unique constraint in MySQL, you have to drop an index rather than the unique constraint itself. The reason, I guess, is that MySQL consider a unique constraint to be an index.

Filed under code Tagged with ,

12 Responses to Drop Unique Constraint in MySQL

  1. Pingback: MySQL Drop Unique Constraint

  2. mathengewachira says:
    January 12, 2009 at 11:22 pm

    thanks for the tip

  3. Alexey says:
    March 26, 2009 at 7:46 am

    CONSTRAINT may be a foreign key.
    in this case, to delete it use:
    alter table table drop foreign key fk_codec_config__codec_group;

  4. Pingback: MySQL Drop Unique Constraint

  5. bang says:
    December 22, 2010 at 10:40 am

    Thank for your help!

  6. Manjeet Singh says:
    August 25, 2011 at 6:54 am

    To add unique key
    ALTER TABLE table_Name ADD UNIQUE(col1,col2,..);

    To Drop Unique key
    Unique key is dropped as INDEX
    First see the name of unique key with show create table table_Name
    Let say unique key name is uniqueKeyName. Normally it is col1 i.e first column name of unique key

    ALTER TABLE table_Name DROP INDEX uniqueKeyName;

  7. Anonymous says:
    April 9, 2012 at 6:32 am

    Thank you

  8. barani says:
    April 16, 2012 at 3:54 pm

    it was really helpful .thanks a lot

  9. Ganesh says:
    April 19, 2012 at 4:31 am

    Thanks for help

  10. Anonymous says:
    September 16, 2012 at 10:34 pm

    thanks! solved a big problem!!!

  11. Yannick Warnier says:
    November 30, 2012 at 12:07 am

    Thanks

  12. Awais Kazimi says:
    September 28, 2013 at 6:28 am

    what about dropping an unique constraint which has not been given any name, e.g. the unique constraint which has been applied this way:
    ALTER TABLE names ADD COLUMN email VARCHAR(255) UNIQUE;
    I have tried all the ways to delete the unique constraint on email column, but nothing working.