Re: 9i: Check- constraint Upper(Column)
Date: Wed, 02 Apr 2008 17:45:21 +0200
Carlos schrieb am 02.04.2008 in
> On 2 abr, 16:54, Andreas Mosmann <mosm..._at_expires-30-04-2008.news- > group.org> wrote:
>> Carlos schrieb am 02.04.2008 in
>>> Not sure about what you're trying to do, but a Function Based Unique >>> Index on (trim(upper(column))) may do the trick...
>> I try to ensure, that it is impossible to insert/update records in the
>> way, that after this there are two records, that differ only by
>> BIG/little - Letters and the number of spaces
>> TeSt EnTrY
>> It should be impossible to insert a record with CMyColumn > 'TEST ENTRY' or
>> 'test entry' or ' test entry ' ...
>> I tried it out:
>> CREATE UNIQUE INDEX MySchema.MyIndexName
>> ON MySchema.MyTable upper(trim((CLOGINNAME)))
>> TABLESPACE MyIndexTableSpace LOGGING;
>> No Problem to create that index, but, after it, still no problem to
>> insert f.e. 'TEST ENTRY'
>> Any more idea?
>> wenn email, dann AndreasMosmann <bei> web <punkt> de
> If you want to avoid INNER blanks, you should add some REPLACE() to > the index expression.
> But If you have successfully created the unique index, you shouldn't > be able to insert 'TEST ENTRY' if you have already inserted 'test > Entry'.
you are particulary right. I did
CREATE UNIQUE INDEX MySchema.MyIndexName ON MySchema.MyTable upper(trim((CLOGINNAME))) TABLESPACE MyIndexTableSpace LOGGING; but in the Oracle Enterprise Manager I the index was only noted as trim(CLOGINNAME), the upper was not to be found. Now I turned the functions around and I can only find upper without trim.
Now it works with upper _and_ with trim, but not with upper(trim()) that means
' Test ' = ' TEST '
' Test' = 'Test '
'Test ' <> ' TEST'
I could write a wrapper function MyFunction as 'trim(upper())' but it sounds crazy ...
Now I tried
CREATE UNIQUE INDEX MySchema.MyIndexName ON MySchema.MyTable trim(upper(trim(CLOGINNAME))) TABLESPACE MyIndexTableSpace LOGGING; and it works ...
The outer function is ignored. Is this my mistake or an oracle bug?
-- wenn email, dann AndreasMosmann <bei> web <punkt> deReceived on Wed Apr 02 2008 - 10:45:21 CDT