Xref: alice comp.databases.oracle.server:73711
Path: alice!news-feed.fnsi.net!newsfeed.direct.ca!su-news-hub1.bbnplanet.com!paloalto-snf1.gtei.net!news.gtei.net!inet16.us.oracle.com!not-for-mail
From: Thomas Kyte <tkyte@us.oracle.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Case-insensitive uniqueness?
Date: Fri, 12 Nov 1999 07:51:13 -0500
Organization: Oracle Service Industries
Lines: 62
Message-ID: <2gwsOPjESn+IQ+emMfL8HUVBZ8kf@4ax.com>
References: <382b9f75$0$233@nntp1.ba.best.com>
Reply-To: tkyte@us.oracle.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: inet16.us.oracle.com 942410996 14679 138.1.114.224 (12 Nov 1999 12:49:56 GMT)
X-Complaints-To: usenet@inet16.us.oracle.com
NNTP-Posting-Date: 12 Nov 1999 12:49:56 GMT
X-Newsreader: Forte Agent 1.6/32.525

A copy of this was sent to David Fetter <dfetter@shell4.ba.best.com>
(if that email address didn't require changing)
On 12 Nov 1999 05:02:45 GMT, you wrote:

>Kind people,
>
>I'd like to make sure that names are unique modulo case.  One way of
>doing this that I thought would be elegant and understandable looks
>like this:
>
>CREATE TABLE foo (
>  foo_id INTEGER NOT NULL PRIMARY KEY
>, foo_desc VARCHAR2(255) NOT NULL
>, UNIQUE(LOWER(foo_desc))
>)
>/
>
>Alas, this is not to be.  Instead of something helpful, Oracle 8.0.5.1
>Enterprise Edition for Linux says:
>
>, UNIQUE(LOWER(foo_desc))
>              *
>ERROR at line 4:
>ORA-00907: missing right parenthesis
>
>What's going wrong here, and how do I fix it?
>

In order to do this in 8.0.5 you will have to add a mirror column
(foo_lowercase) and use a trigger to maintain it -- eg:

create or replace trigger x after insert or update on foo
for each row
begin
   :new.foo_lowercase := :new.foo_desc;
end;


and then create a unique constraint on foo_lowercase.


In Oracle8i, release 8.1, you can instead:

create unique index foo_idx on foo( lower( foo_desc ) );




>Thanks in advance for any help, tips, etc.
>
>Cheers,
>David.


-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte@us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation