Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage


  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats

    • 7,457,268 Views

Conditional Uniqueness in Oracle using FBI

Posted by decipherinfosys on February 1, 2007

We were presented a very typical problem by one of our clients.  They have a store in which they define the different locations.  These locations can be classified into different categories – Reserve locations, Active locations, etc..  According to them the uniqueness of a record in their table varied depending upon the location category i.e. if it is an active location, then the combination of the first three attributes forms the alternate key, if the location category is reserve, then a combination of a separate set of attributes makes the record unique and so on and so forth.  And since they had a legacy system, they were storing all these in a single physical table.  We were clearly told that the re-design and normalization of this structure is out of question since that would involve a lot of application changes.  But we did need to implement a way of maintaining the data integrity i.e. do not allow duplicates into the table based on the location categories.

This client was using Oracle and Oracle has a very neat feature called : FBI (Function Based Indexes).  We have posted about it in the past as well – you can search for it on our blog or on our site. So, let’s see how we can enforce “conditional” uniquess using a FBI in Oracle.

CREATE UNIQUE INDEX LOCATION_UX_IND_1 ON LOCATION (CASE WHEN Location_Category = ‘A’ THEN POSITION||AISLE||AREA
WHEN Location_Category = ‘O’ THEN POSITION||AISLE||BAY||AREA
WHEN Location_Category = ‘Q’ THEN AREA||ZONE
WHEN Location_Category = ‘R’ THEN POSITION||AISLE||BAY
WHEN Location_Category = ‘S’ THEN POSITION||BAY||AREA
ELSE NULL
END)
TABLESPACE LARGETBS
PCTFREE 15
INI_TRANS 40;

This served the business needs without requiring a re-design.  And since this table has a static list of locations in which the records get updated only very rarely, this approach was fine for performance as well.

Sorry, the comment form is closed at this time.

 
<span>%d</span> bloggers like this: