Adding new field with Unique key index in existing MySQL table

Question

I am trying to add a column with Unique Key (so it will not have duplicate records) in existing MySQL table that contains multiple rows of data.

ALTER TABLE  `common`.`fraud_payment_log`
  ADD  `retainer_id` VARCHAR( 20 ) NOT NULL,
  ADD  `http_referrer` VARCHAR( 255 ) NULL ,
  ADD UNIQUE (`retainer_id`);

But it is throwing below error:

ERROR 1062 (23000): Duplicate entry '' for key 'retainer_id'

The error is because of the duplicate empty value which will come when we adding a new column in the existing table with records.

Can anyone please suggest how to achieve this?

You could be interested by http://stackoverflow.com/a/512485/14673 — Luc M, Jul 09 '14 at 12:38
possible duplicate of [MySQL: Creating new unique field in already populated table](http://stackoverflow.com/questions/8733573/mysql-creating-new-unique-field-in-already-populated-table) — Marcus Adams, Jul 09 '14 at 13:16

score 3 · Answer 1 · answered Jul 09 '14 at 12:35

You cannot add a unique, non-NULL column to a table that has more than one row. By definition, two rows would get the same value.

Add the columns first, allowing NULL values:

ALTER TABLE  `common`.`fraud_payment_log`
    ADD  `retainer_id` VARCHAR( 20 ) NULL,
    ADD  `http_referrer` VARCHAR( 255 ) NULL;

Now, populate the column so it has different values. Say:

update `common`.`fraud_payment_log` cross join
       (select @rn := 0) vars
     set retainer_id = (@rn := @rn + 1);

Then add the unique constraint. I usually do this with the index directly:

create unique index idx_fpl_retainer on  `common`.`fraud_payment_log`(retainer_id);

If the table is empty, then just recreate the table with all the columns you want.

score 0 · Answer 2 · answered Jul 09 '14 at 12:35

0

Follow this steps:

Add new field which is not unique
Update table and put unique values in that field
Alter table to make this field as unique

answered Jul 09 '14 at 12:35

miltos

1,009
1
6
10

score 0 · Answer 3 · answered Jul 09 '14 at 12:35

0

You should first add the column, then populate it with the unique values, then add the UNIQUE constraint.

answered Jul 09 '14 at 12:35

Quassnoi

413,100
91
616
614

score 0 · Answer 4 · answered Jul 09 '14 at 12:35

0

You should specify a DEFAULT NULL; NULL doesn't count against UNIQUE checks, so your table will be first filled with NULL values, then you'll proceed to fill it as you wish.

answered Jul 09 '14 at 12:35

Alessandro Lai

2,254
2
24
32

score 0 · Answer 5 · answered Mar 04 '21 at 05:48

Create new column using UI or query(My new column is sensorID)
Fire this :

UPDATE client set sensorID=id

In above query client is my table , sensorID is my newly added column on which I wanted to apply unique, id is my primary key type column in existing table.

Adding new field with Unique key index in existing MySQL table

5 Answers5