如何在不删除唯一约束的前提下处理唯一约束违反问题

原触发器失效原因 #

你写的BEFORE行级触发器未生效主要有两个核心原因：

• 声明了PRAGMA AUTONOMOUS_TRANSACTION自治事务后，触发器内的DELETE操作属于独立事务，你没有显式提交的话DELETE操作不会真正生效；就算补充了COMMIT，并发插入同主键数据时仍会出现校验冲突。
• 约束校验和触发器执行逻辑的顺序在多事务并发场景下会出现不可预期的结果，确实可能出现你提到的冲突发生在DELETE执行前的问题。

更优雅的实现方案 #

完全不需要删除原有唯一约束，可根据你能否修改插入程序的SQL选择对应方案：

方案1：MERGE语句实现Upsert（可修改插入SQL时优先选） #

原生支持插入/更新二选一逻辑，不会触发唯一约束冲突，也不需要额外的触发器或表结构修改：

MERGE INTO DC_DEVICE t
USING (
    SELECT 
        :NEW_DEVICE_ID AS DEVICE_ID_PK,
        :COL2_VAL AS COL2,
        :PROG_FLAG AS PROG_FLAG -- 插入程序的特殊标识
    FROM DUAL
) s
ON (t.DEVICE_ID_PK = s.DEVICE_ID_PK)
-- 匹配到主键时，如果是指定程序的插入请求则覆盖原有数据，否则不做操作
WHEN MATCHED AND s.PROG_FLAG = '特殊标识值' THEN
    UPDATE SET t.COL2 = s.COL2 -- 按需更新需要覆盖的字段
-- 未匹配到主键时直接插入
WHEN NOT MATCHED THEN
    INSERT (DEVICE_ID_PK, COL2, PROG_FLAG)
    VALUES (s.DEVICE_ID_PK, s.COL2, s.PROG_FLAG);

方案2：插入加忽略重复主键提示 #

如果不需要覆盖原有数据，仅需要跳过冲突行不抛错，可直接使用Oracle的内置提示：

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(DC_DEVICE, 你的唯一约束名称) */
INTO DC_DEVICE (DEVICE_ID_PK, COL2, PROG_FLAG)
VALUES (:NEW_DEVICE_ID, :COL2_VAL, :PROG_FLAG);

方案3：DML错误日志（完全无法修改插入SQL时使用） #

如果完全不能修改插入程序的代码，可通过Oracle内置的DML错误日志功能捕获冲突行，不会导致整个插入事务中断，也不影响原有唯一约束的使用：

1. 先创建错误日志表：

BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG(
    dml_table_name => 'DC_DEVICE',
    err_log_table_name => 'DC_DEVICE_INSERT_ERR'
  );
END;
/

2. 插入语句新增错误日志配置即可：

INSERT INTO DC_DEVICE (DEVICE_ID_PK, COL2, PROG_FLAG)
VALUES (:NEW_DEVICE_ID, :COL2_VAL, :PROG_FLAG)
LOG ERRORS INTO DC_DEVICE_INSERT_ERR ('主键重复冲突') REJECT LIMIT UNLIMITED;

不推荐删除原约束的方案 #

原有唯一约束是数据库层面原生的数据一致性保障，性能和可靠性远高于触发器实现的校验，删除后会导致原有依赖约束的程序出现数据重复的风险，非必要不要采用。

内容的提问来源于stack exchange，提问作者MathieuAuclair