Using unique function based index as check constraint 20188367001

Using unique function based index as check constraint

July 14, 2006 PL/SQL 3 Comments

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

                                 )

                         );

Related Posts

About The Author

Jurgen Kemmelings

Oracle consultant at AMIS

3 Comments

  1. Piyush February 23, 2009

    please find the solution at –
    http://plsqlnotes.blogspot.com/search/label/Function%20Based%20Index

  2. Serguey Kavafian February 15, 2008

    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.

  3. Robert Willems July 14, 2006

    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