Creating Unique Index in MySQL on table with duplicate rows


Many a times a situation arrives when you would like to add unique keys on a table after the table is already being used by some application. This means the data is already inserted in the table with a possibility of duplicate entries on the key for which you want unique records.


set session old_alter_table=1

alter ignore table <table name> add unique (<col1,col2,…>)


The first step mentioned above is applicable only in case the table is using InnoDB Engine. The issue is that the IGNORE keyword gets skipped for the alter statement if InnoDB Engine is used.  The bug which details this out is here.