Adding Composite Unique key and Duplicate Entry error

Question

In a pivot table I need to add composite unique key on two big-int fields.

ALTER TABLE table_name ADD UNIQUE KEY `uk_field1_field2` (field1, field2);

The query reports error

Duplicate entry '24014-1435' for key 'uk_field1_field2'

This is the last record in the table with these values I what guess form '24014-1435'. I have confirmed that combination is unique. If I remove that particular record, the error then points to second last row with the same error. Until I flush all the records, I cannot add unique key.

Can I add unique key constraints on table without flushing all rows?

EDIT

Table: table_name
Columns:
id bigint(20) UN AI PK
created_at timestamp
updated_at timestamp
applied_at timestamp
field1 bigint(20)
field2 bigint(20)
field3 bigint(20)
status varchar(64)

Example Data

'2214', '2016-01-05 13:51:03', '2016-01-05 13:51:03', '2016-01-05 13:51:02', '1972', '24', '1155', 'applied'
'2215', '2016-01-05 13:51:05', '2016-01-05 13:51:05', '2016-01-05 13:51:03', '1972', '31', '2137', 'applied'
'2216', '2016-01-05 13:51:06', '2016-01-05 13:51:06', '2016-01-05 13:51:05', '1972', '33', '2487', 'on-hold'
'345', '2016-01-05 08:50:07', '2016-01-05 08:50:07', '2016-01-05 08:50:07', '1717', '54', '4602', 'on-hold'

can you provide a small example (table definition and data) that will reproduce your problem? — Paul Spiegel

ScaisEdge ScaisEdge · Accepted Answer · 2016-03-27T09:39:20

THe key echo could be not the key responsible of the violation ..

There is no guarantee that the valueaaaaaaaaaaaaaaa-11111 in the message

[23000][1062] Duplicate entry 'aaaaaaaaaaaaaaa-11111' for key 'mykey'

is the value that actually causes the violation. Seems to be a bug in MariaDB and evtl. in MyS

see this https://dba.stackexchange.com/questions/106294/add-unique-index-fails-with-duplicate-entry-error-but-no-duplicates-found

You can try adding a not unique index and then investigate for the keys with problem ..

Try investiganting with

select field1, field2 count(*) group by  field1, field2
having count(*) >1;

Adding Composite Unique key and Duplicate Entry error

1 Answers