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)
(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?
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 :)