How to add an UNIQUE field to existing table with data for my table using mySQL syntax

Question

i`d like to add new unique field to my table but when i run command like

ALTER TABLE b_iblock_element ADD `XML_ID_UNIQUE` INT NOT NULL UNIQUE AFTER `XML_ID`

I have an error

Duplicate entry '0' for key 'XML_ID_UNIQUE'!

How can I ignore this error? Maybe there`re another commands in mySQL?

Answer 1

If you have data in the table, you need to do this in three steps:

-- add the column, with no unique constraint
ALTER TABLE b_iblock_element
    ADD `XML_ID_UNIQUE` INT NOT NULL AFTER `XML_ID`;

-- assign the values in the column some unique values
UPDATE b_iblock_element
    SET XML_ID_UNIQUE = <something unique>;

-- add in the unique constraint
ALTER TABLE b_iblock_element ADD CONSTRAINT unq_XML_ID_UNIQUE UNIQUE (b_iblock_element);

<something unique> could be assigned as:

UPDATE b_iblock_element CROSS JOIN
       (SELECT @rn := 0) params
    SET XML_ID_UNIQUE = (@rn := @rn + 1);

But you might have some other way of assigning values.