How to create unique index on fields with possible null values (Oracle 11g)?

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)

or

(3, NULL, 7)
(4, 123, 7)

or (Note: this condition is not allowed in unique index nor unique constraint)

(5, NULL, 7)
(6, NULL, 7)

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.

Is it possible to let db check the uniqueness of these 2 columns only when both are not null?

Answers


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

you can use the nvl function to avoid nulls and place a different value instead ,

create unique index func_idx on TEST_TABLE (nvl(UNIQUE_VALUE,1), UNIQUE_GROUP_ID);

the disadvantage is that your index will be larger and if you would like to search for null values you will have to use the nvl function in order to avoid table_access_full.

also all of the null values will be located under one branch in the index , so make sure your histograms are updated.

I Hope this will help you :)


Need Your Help

Chrome on android resizes font

android html css google-chrome font-size

Apparently once paragraph of text reaches a certain length, chrome on android decides to resize the text and make it larger (causing all kinds of fun). Now I have a website where about half of the ...

Combine two Images into one new Image

c# .net image bitmap

I have two JPEG files with different dimensions: