Re: using oracle functions in constraints

From: Joel R. Kallman <jkallman_at_us.oracle.com>
Date: 1996/12/03
Message-ID: <01bbe0d6$5b52d940$e7041990_at_jkallman>#1/1


I guess I don't see why you *do not* want to define this as a trigger. Based upon your requirements, *something* is going to have to query the table, performing UPPER() on the value and the column, to see if it exists.  The trigger is quite simple to implement, you have control over what errors/messages are returned to your application, they can be easily disabled.

Joel

-- 
Joel R. Kallman		See Oracle products in action!!  http://govt.us.oracle.com
Oracle Government
jkallman_at_us.oracle.com
----
The statements and opinions expressed here are my own 
and do not necessarily represent those of Oracle Corporation.

mvani_at_giascl01.vsnl.net.in wrote in article
<849503098.14253_at_dejanews.com>...

> Hullo Oracle Gurus,
> This is should be a fairly simple question for experienced Oracle
> users. So *do* help me! :)
> In a table there is a char/varchar field. It can accept both upper
> and lower case letters. *But* I need to specify a case-insensitive
> unique constraint, ie if 'Oracle' is entered in one tuple of the table
> , 'ORACLE' should not be accepted in another tuple. How do I go about
> it. I think it is a very basic requirement of a database. I would like
> to define it in the table itself instead of checking in a trigger/
> procedure. Imp : the field *should* be allowed to have both cases, so
> converting to all upper at the application/client end does not help.
> Maybe an elegant solution is there in Oracle, I just haven't found it.
> I tried - create table x (x char(15), constraint x_unique unique
> (upper(x)) ); but it did not work.
> A million TIA,
> Vani
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
>
Received on Tue Dec 03 1996 - 00:00:00 CET

Original text of this message