CODEWITHSUNDEEP
oracle-database
Abbos Bukaboyev
Abbos Bukaboyev

Reputation: 23

Oracle unique index with condition

When I am learning oracle index, I encountered with the situation that I am not able to understand.

create unique index u_index1 on table_a(case when code is not null then company_id end, code)

I am not able to understand the purpose of this code, Does this index mean that, if code is not null then index on code, company_id is created? or if code is null then index is created on code column only? or what?

Upvotes: 0

Views: 123

Answers (1)

Popeye
Popeye

Reputation: 35900

The meaning of this index(ultimately unique constraint) is:

When you insert NULL into code then uniqueness will be checked on (NULL, CODE). Ok but code is also NULL so index will be on (NULL, NULL). means on nothing.

When code is not null then index will be on (COMPANY_ID, CODE)

Now, let's take this in this way: COMPANY_ID can have multiple duplicates with NULL CODE but for a single COMAPNY_ID, There must be a unique CODE

Cheers!!

Upvotes: 1

Related Questions