如何为表创建基于TYPE_ID与STATUS列的唯一约束（限定Active行唯一）

需求明确 #

每个TYPE_ID仅允许存在1条STATUS=1（激活）的记录，STATUS=0（未激活）的记录可重复创建。

可行方案 #

方案1：函数型唯一索引（兼容所有Oracle版本） #

通过创建基于CASE函数的唯一索引，仅对激活状态的记录强制执行唯一性限制：

CREATE UNIQUE INDEX UIX_TEST_TBL_TYPEID_ACTIVE 
ON TEST_TBL (
    CASE WHEN STATUS = 1 THEN TYPE_ID ELSE NULL END
);

• 原理说明：当STATUS=1时，索引会存储对应的TYPE_ID值，唯一索引特性会阻止同一个TYPE_ID重复出现；当STATUS=0时，索引存储的是NULL，而Oracle的唯一索引允许多个NULL值存在，因此不会限制未激活状态的重复记录。

方案2：条件唯一约束（Oracle 12c及以上版本可用） #

如果使用的是Oracle 12c或更高版本，直接定义带条件的唯一约束更直观：

ALTER TABLE TEST_TBL
ADD CONSTRAINT UK_TEST_TBL_TYPEID_ACTIVE
UNIQUE (TYPE_ID)
WHERE (STATUS = 1);

该约束仅对STATUS=1的记录生效，与函数型索引效果完全一致，但属于表级约束，可读性更强。

测试验证 #

用你提供的测试场景验证规则生效情况：

-- 插入两条TYPE_ID=1、STATUS=0：执行成功
INSERT INTO TEST_TBL (TYPE_ID, STATUS) VALUES (1, 0);
INSERT INTO TEST_TBL (TYPE_ID, STATUS) VALUES (1, 0);

-- 插入一条TYPE_ID=1、STATUS=1：执行成功
INSERT INTO TEST_TBL (TYPE_ID, STATUS) VALUES (1, 1);

-- 再次插入TYPE_ID=1、STATUS=1：触发唯一约束报错，符合规则要求
INSERT INTO TEST_TBL (TYPE_ID, STATUS) VALUES (1, 1);

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