« 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

 

 


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>