« The empire strikes back! | Main | InfoQ : Running the Largest Hadoop DFS Cluster »
Friday
May172013

Conditional Unique Indexes


Matrix : What you must learn is that these rules are no different than rules of a computer system. Some of them can be bent, others can be broken. Understand?
 
Usually an unique index grants the uniqueness of all rows in a specific table which have non-null values; But what if some data depended on a given business type in the the row of table needs to be unique and some not?
combining function based index feature from Oracle with a unique index makes this possible.

--
-- SCOPE : OASIS
--
-- DDL:CALLITEMS_UK01           :INDEX.MOD              - TEST
--

DROP   INDEX CALLITEMS_UK01;
CREATE UNIQUE INDEX CALLITEMS_UK01
       ON    CALLITEMS
             ( CASE UNIQUE_REF WHEN 1 THEN UPPER(TRIM(ITEMCODE)) ELSE NULL END
             , CASE UNIQUE_REF WHEN 1 THEN ITEMTYPE ELSE NULL END
             , CASE UNIQUE_REF WHEN 1 THEN ACTIVESINCE ELSE NULL END  )
  TABLESPACE OASISIXM
;

 

Dependend on the uniqeness flag UNIQUE_REF a row may be unique to others or not. Maybe this makes sense for specific call item types. In our project a CALLITEMTYPES table controlled the unqueness of specifc call item types , populating the UNIQUE_REF flag to the CATLLITEMS table.

cheers

/K

 

 


References (1)

References allow you to track sources for this article, as well as articles that were written in response to this article.

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>