Wednesday, 27 April 2011

Using an Oracle Function Index as a Constraint

Two previous blogs of mine have talked about Oracle’s 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,
char(1) default 'Y',
  created_on timestamp not

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 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:


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, 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

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.

No comments: