How to make unique constrant on a column case-INsensitive in Oracle 9

[Genna]

I have a task:
set unique constraint on a table column but I don't care about case
sensitivity. How to implement it? I know Oracle 10 has NLS_SORT=xxxx_CI
setting with 'CI' suffix that is 'Case Insensitive' just to address the
issue. But I need to solve this for Oracle 9.
Thanks in advance.
Genna.

[IANAL_VISTA]

"Genna" <gralko@gmail.com> wrote in
news:1129324111.358555.207850@g44g2000cwa.googlegroups.com:
I have a task: set unique constraint on a table column but I don't care about case sensitivity. How to implement it? I know Oracle 10 has NLS_SORT=xxxx_CI setting with 'CI' suffix that is 'Case Insensitive' just to address the issue. But I need to solve this for Oracle 9. Thanks in advance. Genna.

Use either UPPER() or LOWER() functions in the WHERE clause

[Genna]

thx for your comment. it's actually the way I have it implemented right
now. added this UPPER() check in a table trigger and made it fire a
custom error. Does anybody know a nicer and more efficient way to do
this case-INsensitive unique constraint on a table column (not the
whole table or database).

[Sybrand Bakker]

On 24 Oct 2005 07:45:37 -0700, "Genna" <gralko@gmail.com> wrote:
thx for your comment. it's actually the way I have it implemented rightnow. added this UPPER() check in a table trigger and made it fire acustom error. Does anybody know a nicer and more efficient way to dothis case-INsensitive unique constraint on a table column (not thewhole table or database).

An ordinary check constraint would do. CHECK (column = UPPER(column))

--
Sybrand Bakker, Senior Oracle DBA

[Marek Läll]

create a unique function-base index:
create index <name> on <table> (upper(<column>))

regards,
Marek.