如何实现仅当project_id列唯一时执行SELECT，否则抛出报错

要实现「仅当project_id全唯一时返回查询结果，否则抛出指定错误」的需求，不能直接用CASE WHEN（它是行级逻辑，无法基于全局统计结果控制整个查询的执行分支），需要先对表做全局唯一性校验，再根据校验结果决定返回数据还是抛出错误。以下是主流数据库的具体实现方案：

PostgreSQL 实现 #

方式1：纯查询语句实现 #

通过CTE先完成唯一性统计，再结合raise_exception函数触发错误：

WITH uniqueness_check AS (
    SELECT 
        COUNT(DISTINCT "project_id") AS distinct_count,
        COUNT("project_id") AS total_count
    FROM "projects"
)
SELECT "project_id" AS "ID"
FROM "projects"
WHERE (SELECT distinct_count = total_count FROM uniqueness_check)
UNION ALL
SELECT raise_exception('The IDs do not have unique values')
FROM uniqueness_check
WHERE distinct_count != total_count;

方式2：PL/pgSQL块实现 #

适合需要封装逻辑的场景，先校验再执行查询：

DO $$
DECLARE
    distinct_count INT;
    total_count INT;
BEGIN
    SELECT COUNT(DISTINCT "project_id"), COUNT("project_id")
    INTO distinct_count, total_count
    FROM "projects";
    
    IF distinct_count != total_count THEN
        RAISE EXCEPTION 'The IDs do not have unique values';
    END IF;
END $$;

-- 校验通过后执行查询
SELECT "project_id" AS "ID" FROM "projects";

MySQL 实现 #

通过存储过程封装校验和查询逻辑，使用SIGNAL抛出自定义错误：

DELIMITER //
CREATE PROCEDURE GetUniqueProjectIDs()
BEGIN
    DECLARE distinct_count INT;
    DECLARE total_count INT;
    
    SELECT COUNT(DISTINCT `project_id`), COUNT(`project_id`)
    INTO distinct_count, total_count
    FROM `projects`;
    
    IF distinct_count != total_count THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The IDs do not have unique values';
    END IF;
    
    SELECT `project_id` AS `ID` FROM `projects`;
END //
DELIMITER ;

-- 调用存储过程执行逻辑
CALL GetUniqueProjectIDs();

SQL Server 实现 #

使用变量存储统计结果，通过THROW抛出错误：

DECLARE @distinct_count INT;
DECLARE @total_count INT;

SELECT @distinct_count = COUNT(DISTINCT project_id), @total_count = COUNT(project_id)
FROM projects;

IF @distinct_count != @total_count
BEGIN
    THROW 50001, 'The IDs do not have unique values', 1;
END

SELECT project_id AS ID FROM projects;

为什么CASE WHEN无法实现？ #

CASE WHEN是行级条件判断，只能针对每一行数据返回不同结果，无法基于整个表的统计结论（比如总数量和唯一值数量是否相等）来决定是否执行整个查询或抛出全局错误，因此必须先做全局统计再分支处理。

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