Using unique function based index as check constraint

Case: In a table there is a column customer_code and a column status. The allowable values of status are I, P, C, E. The status indicates that a record is just inserted (I), in process (P), completed (C) or in error (E). The constraint that has to be implemented: the combination of customer_code and (status = I or status = P) must occur only once.

The ‘traditional’ way of implementing this constraint would be to create a set of triggers and to do the check in the after statement trigger.

But it can also be implemented using a unique function based index:

create unique index cps_i2
on orm_customer_payments ( decode(status, 'I', customer_code
, 'P', customer_code
, null
)
);

3 thoughts on “Using unique function based index as check constraint

  1. Thank you for the “Using unique function based index as check constraint”. It helps a bit but there are still not possible to create more complex constrains.

  2. Hi Jurgen,

    thanks for the pointer. I was doing it the different way around. Creating a materialized view that contained the function as a column and creating a unique key on that materialized view. Talk about the long way around ;-), this will make it a lot ‘smoother’.

    Regards,
    Robert

Comments are closed.

Next Post

Mastering EJB3.0 - Free PDF Book Download

Facebook0TwitterLinkedinTheserverside is offering a free copy of Mastering EJB3.0. Published in July 2006, the best selling book Mastering EJB is now in its fourth edition and has been updated for EJB 3.0. This edition features chapters on session beans and message-driven beans, EJB-Java EE integration and advanced persistence concepts. In-depth […]