Re: Unique vs UPPERCASE

From: Peter Laursen <pl_at_mail1.remove.this.stofanet.dk>
Date: Tue, 13 Nov 2001 19:46:59 +0100
Message-ID: <3bf16bcf$0$11606$ba624c82@nntp01.dk.telia.net>

"elveru" <elveru_at_yahoo.com> wrote in message news:9srlq4$hu9$1_at_diana.bcn.ttd.net...
> Hello:
>
> I need a column unique, but unique without UPPERCASE or
lowercase, example:
>
> NAME (unique)

ADD
> ....
> BILL

Main Street
> Bill <<<===

Washington Street
>
> I need that this situation isn't possible.
>

In a a system (on 8.0.5) that I recently designed a number of tables had a column NAME. The values in this should be unique regardless of case - If 'BILL' was there then 'Bill' could not be there. However the names were required to be shown as typed by the user.
I made no constraints on the NAME column but made a NAME_UPPER column that is populated with upper(NAME) by a on insert or update row level trigger and NAME_UPPER has a unique constraint. Values from NAME is used to display. All selects are done on NAME_UPPER.
It works fine and you wont have an application or programmer 'forget' and insert non unique data.
The drawbacks are a performence penalty due to the trigger and extra space usage due to double storage of names.

Peter Laursen Received on Tue Nov 13 2001 - 12:46:59 CST