Using an Oracle Function Index as a Constraint

Two previous blogs of mine have talked about Oracle’s Function Based Indexes:

• Oracle Function Based Indexes
• Enabling Oracle Function Based Indexes

In what should be the final blog on the subject I’m going to cover a more complex use of function based indexes to enforce a rule-based constraint.

In this scenario we’re considering a simple product table that has, amongst other columns, a product ID column and an ‘enabled’ column. In our fictitious company, all product numbers must be unique and, upon creation, they are enabled and timestamped. Products can subsequently be disabled, but NOT re-enabled. If a product is re-enabled it is re-added to the table with a new timestamp.

The product table creation SQL should go something like this:

-- Create the table
create table product_id
  id varchar2(10) not null,
  enabled char(1) default 'Y',
  created_on timestamp not null
);

In the table SQL above, the id column is the product id, created_on is a simple timestamp and enabled is set to ‘Y’ or ‘N’. Given our constraint above, then for every product_id.id value, the table can contain many values where product_id.enabled = ‘N’, but only one value where product_id.enabled = ‘Y’. For a given product id of ‘1234’, some sample data may look like this:

id	enabled	created_on
1234	N	12-AUG-1997
1234	N	26-JAN-2001
1234	N	15-NOV-2005
1234	N	23-FEB-2003
1234	Y	01-MAR-2011

In order to enforce our rule we can use an Oracle function based index as a constraint. This is set up with the following SQL:

create unique index product_index
on product_id ( decode(enabled, 'Y', id , null));

Here, I’m using the decode() function to search the enabled column for a ‘Y’. If found then we return the current product_id.id, if not found then we return null, where returning null means that the constraint has not been broken.

To test this out insert a row:

-- insert a new value
insert into product_id (id,created_on)
    values('1234',SYSTIMESTAMP);

This will work; however if your run the same SQL again, then you’ll get the following error:

ORA-00001: unique constraint (SCOTT.PRODUCT_INDEX) violated
(0 rows affected)

To check that you can update the product id, disable the current product id:

update PRODUCT_ID set enabled = 'N'
  where id='1234' and enabled ='Y';

and run the insert SQL again, which will now succeed.