Drop Unique Constraint in MySQL

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.

About these ads

12 Responses to Drop Unique Constraint in MySQL

  1. Pingback: MySQL Drop Unique Constraint

  2. mathengewachira says:

    thanks for the tip

  3. Alexey says:

    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:

    Thank for your help!

  6. Manjeet Singh says:

    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:

    Thank you

  8. barani says:

    it was really helpful .thanks a lot

  9. Ganesh says:

    Thanks for help

  10. Anonymous says:

    thanks! solved a big problem!!!

  11. Yannick Warnier says:

    Thanks

  12. Awais Kazimi says:

    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.

Follow

Get every new post delivered to your Inbox.

Join 34 other followers

%d bloggers like this: