oracle11g - 甲骨文 : Create unique index but ignore NULLs

Question

我在 SO 上遇到了这个例子它提供了一种通过忽略空值来创建唯一索引的解决方案。但是，我想扩展它，但无法找到解决方案。

我有一个表的 3 列的复合索引(表中还有其他 10 列)。这些列不是 PK 的一部分。在这 3 列中，2 将始终保留一些值，而第 3 列可能为 NULL。我有大量的测试数据，并且有很多插入，其中 2 列具有相同的值，第三列为 NULL。这些所有插入都适用于 PostgreSQL，但 Oracle 提示。为了让我的测试用例正常工作，我认为最简单的解决方案是尝试为 Oracle 建立一个唯一索引，该索引可以像在 PostgreSQL 中一样工作。

确切地说:我想要以下类型的构造，不知道如何组合 col1 + col2 + col3

create unique index tbl_idx on tbl (nvl2(col3, col1 + col2, col1 + col2 + col3))

我正在使用 liquibase。索引按以下方式创建 -

<changeSet dbms="postgresql,oracle" author="abc" id="222">
    <createIndex indexName="Index_7" schemaName="ss" tableName="Users" unique="true">
        <column name="idOrganization"/>
        <column name="strUsername"/>
        <column name="strNotDeleted"/>
    </createIndex>
</changeSet>

我正在使用 liquibase 创建我的测试数据，这里有两个插入语句

<insert schemaName="ss" tableName="Users">
    <column name="strUsername" value="user1" />
    <column name="idUser" valueNumeric="20741" />
    <column name="idOrganization" valueNumeric="4" />
    <column name="strFirstName" value="user" />
    <column name="strLastName" value="one" />
    <column name="strEmail" value="email@foo.com" />
    <column name="strNotDeleted" />
</insert>
<insert schemaName="ss" tableName="Users">
    <column name="strUsername" value="user1" />
    <column name="idUser" valueNumeric="20771" />
    <column name="idOrganization" valueNumeric="4" />
    <column name="strFirstName" value="user" />
    <column name="strLastName" value="one" />
    <column name="strEmail" value="email@foo.com" />
    <column name="strNotDeleted" />
</insert>

这 2 个插入对于 PostgreSQL 工作正常，但是对于 Oracle 失败并出现错误“Index_7 约束冲突”。

Answer 1

如果目标只是防止重复，其中 strNotDeleted设置为非 NULL 值，那么您需要这样的基于函数的索引

SQL> create table users(
  2    idOrganization number,
  3    strUsername    varchar2(100),
  4    strNotDeleted  varchar2(3)
  5  );

Table created.


SQL> create unique index idx_users
  2      on users( (case when strNotDeleted is not null
  3                      then idOrganization
  4                      else null
  5                  end),
  6                (case when strNotDeleted is not null
  7                      then strUsername
  8                      else null
  9                 end) );

Index created.

这允许插入您在问题中提到的两行

SQL> insert into users values( 4, 'user', null );

1 row created.

SQL> insert into users values( 4, 'user', null );

1 row created.

您可以在 strNotNull 处插入一行列设置为非 NULL 值

SQL> insert into users values( 4, 'user', 'Yes' );

1 row created.

但是你不能插入第二个这样的行

SQL> insert into users values( 4, 'user', 'Yes' );
insert into users values( 4, 'user', 'Yes' )
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IDX_USERS) violated

在幕后，Oracle b*-tree 索引没有完全索引 NULL条目。两人 CASE语句确保索引只有 idOrganization 的条目和 strUsername如果 strNotDeleted不是 NULL .如 strNotDeleted是 NULL ，然后两者 CASE语句评估为 NULL并且索引中没有条目。从概念上讲，它类似于其他数据库中的部分索引，它允许您指定 WHERE索引上的子句，以便您只索引“有趣”的行。