I have an UNIQUE constraint for a column in Oracle Database table. It is allowed to save the same value with lower and upper case.
For Example; First I insert M100 into the UNIQUE column of the table. Then again when I tried to insert M100, I can see the unique constraint violation error.
But when I try to insert m100, database is accept this and saved into table.
How could I solve this. I need to restrict the value regardless the case.
I am using below Edition
"Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0"
Here is the Live SQL
Since you are using Oracle 18 you can use new features like invisible columns and virtual columns or
collation
:Virtual invisble column + unique constraint: For example, you have a table T with column STR:
So you can add invisible virtual column
str_lower
generated aslower(str)
:Since this column is invisible and virtual, it will not break your existing code. Now you can add
unique
constraint on it:Testing it:
In addition it allows you to easily find values by lower value:
As you can see it doesn't return str_lower column if you not specify it in select-list:
Another possible solution is to specify
collation
for your column, but it requires to set database parameterMAX_STRING_SIZE
toEXTENDED
, otherwise you'll getORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.
More about this: https://oracle-base.com/articles/12c/column-level-collation-and-case-insensitive-database-12cr2