如何在具有可能的空值的字段上创建唯一索引(Oracle 11g)? [英] How to create unique index on fields with possible null values (Oracle 11g)?
问题描述
以下是包含3列(ID,UNIQUE_VALUE,UNIQUE_GROUP_ID)的示例表
Here is the sample table with 3 columns (ID, UNIQUE_VALUE, UNIQUE_GROUP_ID)
我希望以下记录可以允许:
I want below records can be allowed:
(1, NULL, NULL)
(2, NULL, NULL)
或
(3, NULL, 7)
(4, 123, 7)
或(注意: unique index
或唯一约束
)
or (Note: this condition is not allowed in unique index
nor unique constraint
)
(5, NULL, 7)
(6, NULL, 7)
$ b b
并且不允许这些:
and these can't be allowed:
(7, 123, 7)
(8, 123, 7)
我在最后两列创建了唯一索引,但只有前两个示例被)允许。
I created a unique index on last 2 columns, but only the first 2 examples can be allowed.
只有当两个列都不为空时,才允许db检查这两列的唯一性?
Is it possible to let db check the uniqueness of these 2 columns only when both are not null?
推荐答案
您只希望在 UNIQUE_VALUE
和 UNIQUE_GROUP_ID
不为null。为此,您可以使用一个独特的基于函数的索引:
You want to only enforce uniqueness on the rows where both UNIQUE_VALUE
and UNIQUE_GROUP_ID
are not null. To do this, you can use a unique function-based index:
CREATE UNIQUE INDEX func_based_index ON the_table
(CASE WHEN unique_value IS NOT NULL
AND unique_group_id IS NOT NULL
THEN UNIQUE_VALUE || ',' || UNIQUE_GROUP_ID
END);
这篇关于如何在具有可能的空值的字段上创建唯一索引(Oracle 11g)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!