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