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.

1
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