时间戳

首页 > 解决方案 > MSQL 唯一约束检查​​仅向前插入记录 不存在的记录

mysql - MSQL 唯一约束检查​​仅向前插入记录 不存在的记录

问题描述

该表已经有重复条目。我想在 MQSL DB 中创建一个唯一约束而不删除现有的重复项。如果以后出现任何重复条目，则会显示错误。鉴于打击查询在 MYSQL 中不起作用。

ALTER TABLE presence
ADD CONSTRAINT present uniqueness UNIQUE (employee_id,roll_number) where id >10000;

or 
ALTER TABLE presence
ADD CONSTRAINT present uniqueness UNIQUE (employee_id,roll_number) where id <> (343,34534,34534)

我们在 SQL 中有类似的解决方案吗？

标签： mysqlsqlunique-constraint

解决方案

Add an additional column to the table that indicates the existing values.

Set it to NULL for the existing values. And give it a constant value, say 1, for the new rows. Then create a unique index or constraint on this column:

alter table t add constraint unique (employee_id, is_old)

Actually, I realize that you probably don't want duplicates with singleton old values and new values. That is just an issue of setting the value to NULL only for duplicates in the history. So, one row would have a constant value (say 1) in the historical data.

MySQL allows duplicate values on NULL, which is why this works.

推荐阅读