Oracle: Create unique index but ignore NULLs

Question

I came across this example on SO which gives a solution to create a unique index by ignoring nulls. However, I want to extend it and I am not able reach to a solution.

I have a composite index for 3 columns of a table (there are other 10 columns in the table). These columns are not part of PK. Of these 3 columns, 2 will always hold some value and 3rd may be NULL. I have huge test data, and there are many inserts with 2 of the columns with same value and 3rd column NULL. These all inserts work well for PostgreSQL, however Oracle complains. For my test cases to work, I think the simplest solution that I think is to try a unique index for Oracle that would work as it works in PostgreSQL.

Precisely: I want a construct of following kind, not sure how to combine col1 + col2 + col3

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

I am using liquibase. Index is created in following way -

<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>

I am using liquibase to create my test data, here are two insert statements

<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>

These 2 inserts work fine for PostgreSQL, however fail for Oracle with error "Index_7 constraint violation".

I'm not sure I understand the problem you are trying to solve. If you created the simplest possible unique constraint, `alter table table_name add constraint uk_table_name unique( col1, col2, col3 )`, what row(s) are excluded that you want to allow and/or what row(s) are included that you want to disallow? — Justin Cave, Aug 09 '12 at 22:53
@JustinCave: Updated my question, I hope it gives a better picture. — devang, Aug 09 '12 at 23:05
What does "Oracle complains" mean, exactly? Is there an error message? If so, what error? A simple composite unique index on `col1, col2, col3` would allow you to insert an unlimited number of rows where `col1` and `col2` are identical and `col3 IS NULL`. It sounds like you are saying that is what you want and that is not what you observe so it would help to post a small test case that shows what you are trying to achieve and what error you get. — Justin Cave, Aug 09 '12 at 23:13
This MUST raise a constraint violation. You are creating a unique index and the values that form the index are absolutely the same; any other behavior would be a bug. It sounds like you don't want a unique index. — steve, Aug 09 '12 at 23:44
Postgres documentation says: `When an index is declared unique, multiple table rows with equal indexed values are not allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple rows.` - Does that mean that the records will give **DIFFERENT INDEXES** if 1 column is NULL and other 2 columns have same value in 2 record instance? — devang, Aug 10 '12 at 00:15
OK. So what is an example of an insert that you would want to fail? Do you only want to prevent duplicates when `strNotDeleted` is set to a non-NULL value? — Justin Cave, Aug 10 '12 at 00:58
Ahh, for Postgres it works because they consider `NULL != NULL`. — devang, Aug 10 '12 at 01:25

Justin Cave · Accepted Answer · 2012-08-10T04:24:37.610

15

If the goal is only to prevent duplicates where strNotDeleted is set to a non-NULL value, then you want a function-based index like this

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.

This allows the two rows you mention in your question to be inserted

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

1 row created.

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

1 row created.

You can insert one row where the strNotNull column is set to a non-NULL value

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

1 row created.

But you then can't insert a second such row

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

Behind the scenes, an Oracle b*-tree index does not index completely NULL entries. The two CASE statements ensure that the index only has entries for idOrganization and strUsername if strNotDeleted is not NULL. If strNotDeleted is NULL, then both CASE statements evaluate to NULL and no entry is made in the index. Conceptually, it's similar to a partial index in other databases which allows you to specify a WHERE clause on your index so that you only index "interesting" rows.

edited Aug 10 '12 at 04:24

answered Aug 10 '12 at 01:05

Justin Cave

227,342
24
367
384

Simple question so that I understand. Is your method of creating index equivalent to `create unique index idx_users on users (idOrganization, strUsername, strNotDeleted)`? – devang Aug 10 '12 at 02:23
@gotuskar - No, this is different. A straight composite index on those three columns would disallow the second and fourth inserts. – Justin Cave Aug 10 '12 at 02:32
In the create index statement, why both cases have same check, i.e. `strNotDeleted is not null`. If your could please explain in detail. – devang Aug 10 '12 at 03:33
I tried writing following query `create unique index NDX_ExternalId_OrgId on "ss"."Users" ((case when ("strExternalId" is not null and "strNotDeleted" is not null) then "idOrganization" else null end) );` but it did not work, I still got violation error. – devang Aug 10 '12 at 05:43
@gotuskar - Where did `strExternalId` come from? What was the `INSERT` that caused the constraint violation? Is the goal, as I asked in the first part of my answer, "to prevent duplicates where strNotDeleted is set to a non-NULL value"? I'm still not sure that I understand exactly what you are trying to allow and what you are trying to disallow. The code I posted handles the one case you've posted thus far but I'm not sure that I fully understand what you're trying to accomplish yet. – Justin Cave Aug 10 '12 at 14:39
The one that you provided worked fine. I will accept that answer. I had one more scenario, wherein I wanted to index only if all three fields are available. Of the three fields, two fields may be null. I was trying out a new query based on your previous one, but that did not work. – devang Aug 10 '12 at 14:41
@gotuskar - Can you give an example of inserts that you would want to succeed and inserts that you would want to fail? – Justin Cave Aug 10 '12 at 14:46
If all 3 columns have values, then its `INDEX` should be unique. `idOrganization` will always have value. `strExternalId` and `strNotDeleted` may be null. So multiple `INSERTS` should work even if same `idOrganization` has same value. For columns `(idOrganization, strExternalId, strNotDeleted)`: `insert into users values( 4, 'user', null );`, `insert into users values( 4, null, null );`, `insert into users values( 4, null, '1' );`, `insert into users values( 4, 'user', '1' );` all should work. But `insert into users values( 4, 'user', '1' );` should not, since an entry exists. – devang Aug 10 '12 at 15:42
@gotuskar - A straight composite index on `idOrganization`, `strExternalId`, and `strNotDeleted` would allow the first 4 `INSERT` operations and reject the last one. Is there an `INSERT` that you want to allow that a straight composite index would reject or an `INSERT` that you want to disallow that a straight composite index would allow? – Justin Cave Aug 10 '12 at 15:47
I can have multiple inserts of the first four kind. I want all of them to pass. Multiple inserts of `insert into users values( 4, 'user', null );` should pass. – devang Aug 10 '12 at 15:52

score 0 · Answer 2 · edited Dec 16 '14 at 11:02

SQL> create table users(
      idOrganization number,
      strUsername    varchar2(100),
      strNotDeleted  varchar2(3)
   )
SQL> /

Table created.

SQL> Create unique index idx_users
    on users(
         (
           case when strNotDeleted is not null
                      then idOrganization
                        else null
             end
          ),
          (
           case when strNotDeleted is not null
                       then strUsername
                       else null
           end
         ),
         (
           case when strNotDeleted is not null
                      then strNotDeleted
                       else null
           end
         )
   )
SQL> /

Index created.

Oracle: Create unique index but ignore NULLs

2 Answers2