How to create a unique index in fields with possible zero values ​​(Oracle 11g)?

Question

Here is an example table with three columns (ID, UNIQUE_VALUE, UNIQUE_GROUP_ID)

I want below valid entries:

(1, NULL, NULL) (2, NULL, NULL) 

or

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

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

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

and they cannot be allowed:

 (7, 123, 7) (8, 123, 7) 

I created a unique index for the last two columns, but only the first 2 examples can be resolved.

Can db be allowed to verify the uniqueness of these two columns only when both values ​​are non-zero?

Answer 1

You only want to ensure the uniqueness of the rows where UNIQUE_VALUE and UNIQUE_GROUP_ID not equal to zero. For this you can use a unique index based on functions:

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

Answer 2

you can use the nvl function to avoid zeros and instead put a different value,

 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 want to search for null values, you will need to use the nvl function to avoid table_access_full.

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

Hope this helps you :)