MySQL – Everything you need to know about Create Index on a table

Whenever you want to create index on a table to show the error of unique contraint violation. so that the duplicate values does not gets inserted into the table. In this case you need to create UNIQUE constraint on a table.


--- Create UNIQUE on a column name to avoid duplicate values
ALTER TABLE TableName ADD UNIQUE (FieldName);

Lets say you want to create UNIQUE on existing table of sales_order in Magento 2 on custom column “reference_number”. You ll run the following MySQL query.

ALTER TABLE sales_order ADD UNIQUE (reference_number);

Then you ll get error

Error in query (1062): Duplicate entry 'SO-SPAE-000102788-1' for key 'reference_number'

The reason of this error is because the table already have duplicate values in column “reference_number” so first you need to find the duplicate and remove/modify those records.


/* Fund duplicate */
select reference_number, entity_id, count(*) from sales_order group by reference_number having count(*) > 1;
select entity_id, reference_number from sales_order where reference_number in (select reference_number from sales_order group by reference_number having count(*) > 1); /* find orders with duplicate values */

To know more about finding and updating the records with duplicate values, please check the link.
Check existing constraint on table
select distinct CONSTRAINT_NAME
from information_schema.TABLE_CONSTRAINTS
where table_name = ‘sales_order’ and constraint_type = ‘UNIQUE’;

Delete existing constraint on table
ALTER TABLE DROP INDEX ; /* To delete an index */
ALTER TABLE sales_order DROP INDEX reference_number;
ALTER TABLE sales_order DROP CONSTRAINT reference_number;