Oracle中如何创建仅限制LIVE状态的条件唯一约束

需求很明确：当Status列值为LIVE时，同一个algo_id不能存在多条LIVE状态的记录；但允许同一algo_id有多个OLD或REMOVED状态的记录。

表结构与示例数据 #

表结构：

TABLE: ALGO
ID : Number(10)  -- 主键
Algo_id              VARCHAR2(30)      NOT NULL,
Status          VARCHAR2(30)     NOT NULL,

示例数据及规则说明：

已尝试的无效方案 #

直接给algo_id和Status加普通唯一约束，会导致同一algo_id无法有多个非LIVE状态的记录，完全不符合需求：

ALTER TABLE ALGO
ADD CONSTRAINT unique_live_algo UNIQUE (algo_id, status );

你问到的直接在唯一约束里加WHERE/WHEN条件的写法，Oracle并不支持这种语法。

可行解决方案：函数式唯一约束 #

Oracle可以通过函数逻辑+唯一约束实现这个带条件的限制，有两种常用方式：

方式1：基于CASE表达式的唯一约束 #

ALTER TABLE ALGO
ADD CONSTRAINT unique_live_algo UNIQUE (
    CASE WHEN Status = 'LIVE' THEN algo_id ELSE ID END
);

逻辑说明：

• 当Status为LIVE时，约束校验algo_id，确保同一个algo_id只能有一条LIVE记录
• 当Status不为LIVE时，用主键ID（每条记录唯一）作为校验字段，这样每条非LIVE记录的校验值都不同，不会限制同algo_id的多条非LIVE记录

方式2：基于函数的唯一索引 #

CREATE UNIQUE INDEX idx_unique_live_algo ON ALGO (
    CASE WHEN Status = 'LIVE' THEN algo_id ELSE NULL END
);

逻辑说明：
Oracle的函数索引不会包含NULL值，所以非LIVE状态的记录不会进入这个索引；只有LIVE状态的记录会以algo_id作为索引键，从而保证同一algo_id只有一条LIVE记录。

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