mysql - 添加复合唯一键和重复条目错误

Question

在数据透视表中，我需要在两个大整数字段上添加复合唯一键。

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

查询报错

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

这是表中的最后一条记录，我猜这些值来自“24014-1435”。我已经确认组合是唯一的。如果我删除该特定记录，则该错误将指向具有相同错误的倒数第二行。在刷新所有记录之前，我无法添加唯一键。

我可以在不刷新所有行的情况下在表上添加唯一键约束吗？

编辑

表:表名
专栏:
id bigint(20) 联合国人工智能 PK
created_at 时间戳
updated_at 时间戳
applied_at 时间戳
field1 bigint(20)
field2 bigint(20)
field3 bigint(20)
状态变量(64)

示例数据

'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'

Answer 1

key 回声可能不是造成违规的 key ..

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

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

您可以尝试添加一个非唯一索引，然后调查有问题的键..

尝试调查

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