Stupid unique index tricks...

I have a case where I need to enforce uniqueness on a combination of columns but only when a flag is set (ACTIVE_FLAG=1). The table also holds history and may have multiple inactive records mixed in but I don't want to keep those from being added to the table.

This can be done by exploiting the fact that null index expressions are not indexed. DECODE and function based index to the rescue! Here is a quick proof of concept with the relevant columns:

-- Enforce unique but only on active records

CREATE TABLE UNIQUETEST (
  PARENT_ID NUMBER, 
  CHILD_ID NUMBER, 
  ACTIVE_FLAG NUMBER
);

CREATE UNIQUE INDEX UNIQUE_ON_ACTIVE_ONLY ON UNIQUETEST ( 
  DECODE(ACTIVE_FLAG, 1, PARENT_ID, NULL),
  DECODE(ACTIVE_FLAG, 1, CHILD_ID, NULL)
);

INSERT INTO UNIQUETEST VALUES (100,500,1);
INSERT INTO UNIQUETEST VALUES (100,500,0);
INSERT INTO UNIQUETEST VALUES (100,500,0);
INSERT INTO UNIQUETEST VALUES (100,500,0);

-- This should fail!
INSERT INTO UNIQUETEST VALUES (100,500,1);

-Dave

Tags: 

Add new comment