基于列值的Oracle唯一约束 [英] Oracle Unique Constraint based on column value

问题描述

我有以下唯一约束

dup_Checklist_QNum UNIQUE (QUESTION_NO, IS_ACTIVE)

我试图防止两个问题在活动时有相同的问题编号（IS_ACTIVE值= 1）。

I am trying to prevent two questions having the same question number while being active (IS_ACTIVE value = 1).

在我第二次修改问题之前，所有内容都很好。

All seemed fine until I had to rev a question for the second time.

QUESTION_NO=1, TEXT="Have you..", REV=1 IS_ACTIVE=0  
QUESTION_NO=1, TEXT="Have you..", REV=2 IS_ACTIVE=0  <-- This should be ok but constraint was violated
QUESTION_NO=1, TEXT="Have you..", REV=3 IS_ACTIVE=1
QUESTION_NO=1, TEXT="Have you..", REV=3 IS_ACTIVE=1 <-- This should be throw constraint exception 

我需要constrint仅当IS_ACTIVE = 1时适用

I need the constrint to only apply when IS_ACTIVE=1

推荐答案

您可以创建一个独特的基于函数的索引

You can create a unique function-based index

CREATE UNIQUE INDEX idx_dup_active
    ON <<table name>>( CASE WHEN is_active = 1
                            THEN question_no
                            ELSE NULL
                        END );

这利用了Oracle b-tree索引不会在叶数据块数据将完全为NULL。

This takes advantage of the fact that Oracle b-tree indexes do not store data where the leaf block data would be entirely NULL.

这篇关于基于列值的Oracle唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！