添加UNIQUE约束前校验列重复值的Oracle及PostgreSQL实现方法

原有脚本报错原因 #

原有脚本报错的核心原因有两点：

• SQL标准执行顺序为 FROM → WHERE → GROUP BY → 聚合计算 → HAVING，聚合函数COUNT(*)的筛选条件不能放在WHERE子句中，必须放在HAVING子句中
• 直接对目标字段分组后赋值，若存在多个重复值分组，会返回多行数据触发「返回行数过多」的异常，需要在外层额外套一层聚合保证仅返回单行结果

Oracle 版本实现 #

DECLARE
    ln_count NUMBER(5) := 0;
BEGIN
    -- 外层套COUNT(*)保证只返回1行结果，统计重复分组的数量
    SELECT COUNT(*) INTO ln_count
    FROM (
        SELECT columnName
        FROM tableName
        GROUP BY columnName
        HAVING COUNT(*) > 1
    );
    
    -- 存在重复值时抛出异常终止执行
    IF ln_count > 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '检测到columnName列存在重复值，无法添加唯一约束');
    END IF;
    
    -- 无重复值则执行添加约束逻辑
    EXECUTE IMMEDIATE 'ALTER TABLE tableName ADD CONSTRAINT conName UNIQUE (columnName)';
END;
/

PostgreSQL 版本实现 #

DO $$
DECLARE
    ln_count INT := 0;
BEGIN
    SELECT COUNT(*) INTO ln_count
    FROM (
        SELECT columnName
        FROM tableName
        GROUP BY columnName
        HAVING COUNT(*) > 1
    ) t;
    
    IF ln_count > 0 THEN
        RAISE EXCEPTION '检测到columnName列存在重复值，无法添加唯一约束';
    END IF;
    
    EXECUTE 'ALTER TABLE tableName ADD CONSTRAINT conName UNIQUE (columnName)';
END $$;

通用预验证方案 #

不需要匿名块逻辑时，可先执行以下兼容Oracle、PostgreSQL的通用SQL，返回结果大于0则代表存在重复值：

SELECT COUNT(*) AS duplicate_group_count
FROM (
    SELECT columnName
    FROM tableName
    GROUP BY columnName
    HAVING COUNT(*) > 1
) t;

内容的提问来源于stack exchange，提问作者Dian van Deventer