« 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 (2)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
  • Response
    Square space and all movements of the betterment are coined and aimed with the right and proper and effectiveness. It is the placed and schemes of the controlled aims it is the primal and oriental tinge and reformed nature. It is the aforesaid and ascertained.

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>