Oracle如何实现带状态条件的多列组合唯一性约束

Oracle原生支持带逻辑判断的函数型唯一索引，可以完美匹配你的需求。Oracle的唯一索引不会校验全NULL的索引条目，利用这个特性就能实现条件唯一性：

CREATE UNIQUE INDEX idx_uniq_doc_file_rev_active
ON 你的表名 (
    CASE WHEN ActiveState != 'X' THEN DocID END,
    CASE WHEN ActiveState != 'X' THEN FileName END,
    CASE WHEN ActiveState != 'X' THEN FileRevision END
);

逻辑说明：

• 当ActiveState = 'X'时，三个CASE表达式返回值全为NULL，这行数据不会进入唯一校验范围，允许任意重复插入
• 当ActiveState != 'X'时，三个CASE返回对应字段的实际值，此时会对三列的组合做唯一校验，重复插入会抛出唯一约束冲突错误

如果希望校验规则更显性化、便于后续维护，可以用11g推出的虚拟列特性实现相同效果：

-- 新增虚拟列，仅非X状态下生成三列组合的拼接值
ALTER TABLE 你的表名 ADD (
    v_uniq_key GENERATED ALWAYS AS (
        CASE WHEN ActiveState != 'X' THEN DocID || '^' || FileName || '^' || FileRevision END
    ) VIRTUAL
);

-- 给虚拟列加唯一约束
ALTER TABLE 你的表名 ADD CONSTRAINT uk_v_uniq_key UNIQUE(v_uniq_key);

注意拼接用的分隔符需要是你的业务字段中不会出现的字符，避免不同值拼接后出现误判。

如果特殊场景无法使用索引/虚拟列方案，可以用行级触发器做校验，但高并发场景下存在幻读风险，可能出现重复数据：

-- 先建查询索引避免全表扫描
CREATE INDEX idx_doc_file_rev_state ON 你的表名(DocID, FileName, FileRevision, ActiveState);

CREATE OR REPLACE TRIGGER trg_check_uniq_doc_file
BEFORE INSERT OR UPDATE ON 你的表名
FOR EACH ROW
DECLARE
    l_exist_cnt NUMBER;
BEGIN
    IF :NEW.ActiveState != 'X' THEN
        SELECT COUNT(1) INTO l_exist_cnt
        FROM 你的表名
        WHERE DocID = :NEW.DocID
          AND FileName = :NEW.FileName
          AND FileRevision = :NEW.FileRevision
          AND ActiveState != 'X';
        IF l_exist_cnt >= 1 THEN
            RAISE_APPLICATION_ERROR(-20001, '非失效状态下DocID、FileName、FileRevision组合不可重复');
        END IF;
    END IF;
END;
/

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