Oracle UNIQUE constraint allows same value in different case

0
votes

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

3
oracleoracle-apexunique-constraintoracle18coracle-apex-20.1

3 Answers

4
votes

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

3
votes

Try adding function based index to the table

CREATE UNIQUE INDEX some_index_name
ON tablename(UPPER(column_name));
0
votes

Both previous answers are fine but there is another option that could make sense depending on the business case. Sometimes your data is case insensitive (eg email addresses) so always store it in the same case by using a trigger.

Then the unique constraint will work AND your data is cleaner. An example of such a trigger is:

create or replace TRIGGER t_biu BEFORE
  INSERT OR UPDATE ON t
  FOR EACH ROW
DECLARE
BEGIN
  :NEW.column_name := UPPER(:NEW.column_name);
END t_biu;