Oracle SQL表所有列可空时如何添加唯一性约束（不建唯一索引）

假设你的表名为your_table，包含列col1、col2、col3（可根据实际列名调整），以下是几种满足你需求的实现方式：

1. 多列组合唯一约束 #

利用多个列的组合来定义唯一性，这是最常规的方法。需要注意的是，Oracle的唯一约束会忽略全空行的重复（因为NULL不等于NULL），如果你的业务允许存在多条全空行，这种方式最直接：

ALTER TABLE your_table
ADD CONSTRAINT uk_your_table_cols UNIQUE (col1, col2, col3);

2. 包含全空行的组合唯一约束 #

如果要求全空行也只能存在一条，可通过NVL函数将NULL转换为一个不会出现在实际数据中的标记值，再基于转换后的值创建唯一约束：

ALTER TABLE your_table
ADD CONSTRAINT uk_your_table_all_unique UNIQUE (
    NVL(col1, '###SPECIAL_NULL###'),
    NVL(col2, '###SPECIAL_NULL###'),
    NVL(col3, '###SPECIAL_NULL###')
);

注意：要确保选择的标记值不会与列的实际业务数据冲突。

3. 虚拟列+唯一约束 #

如果觉得直接在约束里写函数不够直观，可以先创建虚拟列存储转换后的值，再给虚拟列添加唯一约束，效果和方法2一致：

-- 添加虚拟列，自动转换NULL为标记值
ALTER TABLE your_table
ADD col1_transformed VARCHAR2(100) GENERATED ALWAYS AS (NVL(col1, '###SPECIAL_NULL###')) VIRTUAL;

ALTER TABLE your_table
ADD col2_transformed VARCHAR2(100) GENERATED ALWAYS AS (NVL(col2, '###SPECIAL_NULL###')) VIRTUAL;

ALTER TABLE your_table
ADD col3_transformed VARCHAR2(100) GENERATED ALWAYS AS (NVL(col3, '###SPECIAL_NULL###')) VIRTUAL;

-- 给虚拟列添加唯一约束
ALTER TABLE your_table
ADD CONSTRAINT uk_your_table_virtual_cols UNIQUE (col1_transformed, col2_transformed, col3_transformed);

4. 触发器实现唯一性检查（无索引方案） #

如果完全不允许创建任何唯一索引（包括约束自动创建的），可以通过触发器在插入/更新前检查重复数据：

CREATE OR REPLACE TRIGGER trg_your_table_unique_check
BEFORE INSERT OR UPDATE ON your_table
FOR EACH ROW
DECLARE
    duplicate_count NUMBER;
BEGIN
    -- 检查当前行是否已存在（包含NULL的相等判断）
    SELECT COUNT(*)
    INTO duplicate_count
    FROM your_table
    WHERE (col1 = :NEW.col1 OR (col1 IS NULL AND :NEW.col1 IS NULL))
      AND (col2 = :NEW.col2 OR (col2 IS NULL AND :NEW.col2 IS NULL))
      AND (col3 = :NEW.col3 OR (col3 IS NULL AND :NEW.col3 IS NULL));

    IF duplicate_count > 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '数据重复，违反唯一性要求');
    END IF;
END;
/

⚠️ 注意：这种方式会在每次插入/更新时执行全表扫描，数据量大时性能会显著下降，仅推荐小表使用。

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