0

I tried to insert into a table with a unique constraints on 2 columns and encountered the unique constraints error.

Select distinct query returns the following records:

Record 1:
ColA:A001 ColB:TV set A001
Record 2:
ColA:A001 ColB:Tv set A001

Does oracle do case sensitive comparison when validating against unique constraints? For instance, in the above scenario, we can see all the values are the same except the ColB (TV verus Tv). Distinct showes they are two different records whereas unique constraints seems to think they are the same?

Can anyone help to clarify?

Thanks!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
user1008697
  • 1,141
  • 2
  • 10
  • 13
  • Please add the DDL statement for the table and the constraint, the current contents of the table plus the INSERT statement you're trying to execute. – Frank Schmitt Jun 26 '14 at 11:41
  • possible duplicate of http://stackoverflow.com/questions/3944840/create-unqiue-case-insensitive-constraint-on-two-varchar-fields which has an accepted answer – mc110 Jun 26 '14 at 11:45

2 Answers2

1

By default, Oracle is case sensitive. So, TV is not the same as Tv. If the constraint is implemented as:

create unique index table_colb on table(colb)

(or the equivalent in the create table statement), then the two will be different. If you want case insensitivity, then use a functional index:

create unique index table_colb on table(lower(colb))
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Oracle is Case Sensitive You may check The DDL SQL Fiddle Demo.

Check the NLS_COMP ans NLS_SORT

select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_SORT','NLS_COMP');
Blood-HaZaRd
  • 2,049
  • 2
  • 20
  • 43