Oracle UNIQUE constraint allows same value in different case

Question

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

Answer 1

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:

create table t(str varchar2(10));

So you can add invisible virtual column str_lower generated as lower(str):

alter table t add 
      str_lower varchar2(10) invisible generated always as (lower(str)) ;

Since this column is invisible and virtual, it will not break your existing code. Now you can add unique constraint on it:

alter table t add
      constraint t_str_unique_lower
         unique(str_lower) using index;

Testing it:

SQL> insert into t values('M100');

1 row created.

SQL> insert into t values('m100');
insert into t values('m100')
*
ERROR at line 1:
ORA-00001: unique constraint (XTENDER.T_STR_UNIQUE_LOWER) violated

In addition it allows you to easily find values by lower value:

SQL> select * from t where str_lower='m100';

STR
----------
M100

SQL> select str,str_lower from t where str_lower='m100';

STR        STR_LOWER
---------- ----------
M100       m100

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 parameter MAX_STRING_SIZE to EXTENDED, otherwise you'll get ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.

alter table t modify str COLLATE BINARY_CI;
alter table t add constraint t_str_unique unique(str);

More about this: https://oracle-base.com/articles/12c/column-level-collation-and-case-insensitive-database-12cr2