On my Oracle instances, string "aaa" does not equal "AAA". In other words, if I have a table MyTable with a unique index on MyTable's single char(3) field. The commands:
insert into MyTable values ('aaa');
insert into MyTable values ('AAA');
work fine. How do I make the instance case sensitive, so that the second insert above would error with a unique key violation?
Thanks in advance!
04-06-2001, 09:45 AM
index on upper(keyvalue);
04-06-2001, 09:51 AM
For a little more info:
Basically, Oracle has no ability to create a case-insensitive instance, as amazing as that seems! So, before 8i, if you wanted case-insensitive searches, you were guaranteed table scans. With 8i, this has been alleviated, but IMHO, it is still *no excuse* for not providing a case-insensitive option in the database!!
Rant over :)
KMesser is *almost* right. If you want to guarantee that 'aaa' and 'AAA' cannot be inserted into the same table, create a *unique* function-based index on UPPER(colname);
This same index will also allow your case-insensitive searches to be indexed look-ups, providing that the WHERE clause matches the function-based index. ie WHERE UPPER(colname) [=|LIKE|...]...