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