Monday, May 20, 2013

Function-based indexes used as constraints

Let's suppose we want to avoid duplicity in the values of a column produced by a bad checking in a formulary of a program, that ends up allowing to add existing values, with the only difference that the user adds leading/trailing blank spaces, or changes one letter to upper/lower case.

A function based index does not only permits to speed up queries by expresion. I recommend this video for a brief explanation:

A function based index can also be used as a type of constraint. Given the example at the beggining, we could create an index of type "UNIQUE" to prevent duplicity cleaning the value to be inserted and checking its uniqueness.

create unique index INDEX_NAME on TABLE (lower(trim(COL_1)));

Once the index is added to the table, any insertion with a duplicate value (with blank spaces or lower/upper case changed) will throw the follow exception:

SQL Error: ORA-00001: unique constraint (TABLE.INDEX_NAME) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

No comments:

Post a Comment