Re: 9i: Check- constraint Upper(Column)

"Andreas Mosmann" <mosmann_at_expires-30-04-2008.news-group.org> schreef in bericht news:1207151121.77_at_user.newsoffice.de...
> Carlos schrieb am 02.04.2008 in
> <360af7b1-487f-4485-81d7-0d13abd40d3e_at_r9g2000prd.googlegroups.com>:
>
>> On 2 abr, 16:54, Andreas Mosmann <mosm..._at_expires-30-04-2008.news-
>> group.org> wrote:
>>> Carlos schrieb am 02.04.2008 in
>>> <221a3638-5b82-4086-b097-8e06e50bd..._at_x41g2000hsb.googlegroups.com>:
>>>
>>>> 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
>>>
>>> example:
>>>
>>> CMyColumn
>>> ----------
>>> TeSt EnTrY
>>>
>>> It should be impossible to insert a record with CMyColumn > 'TEST ENTRY'
>>> or 'test entry' or ' test entry ' ...
>>>
>>>> HTH.
>>>
>>> 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?
>>>
>>>> Cheers.
>>> Thanks
>>>> Carlos.
>>>
>>> Andreas
>>>
>>> --
>>> 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 '
> but
> '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?
>
>> Cheers.
> thank you
>> Carlos.
> Andreas Mosmann
>
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de

I can reproduce this behaviour on my system.

BTW did you try this? (note the brackets) CREATE UNIQUE INDEX MySchema.MyIndexName ON MySchema.MyTable (upper(trim(CLOGINNAME)))  TABLESPACE MyIndexTableSpace LOGGING;

Works on my 10g database:

SQL> create table mytab(myname varchar2(10));

Table created.

SQL> create unique index myindex on mytab (upper(trim(myname)))

Index created.

SQL> insert into mytab values('smallcaps');

1 row inserted.

SQL> insert into mytab values('SmAllCapS'); insert into mytab values('SmAllCapS')
*
ERROR in line 1:
.ORA-00001: Violating UNIQUE-constraint (TEST.MYINDEX).

Errors may not be accurate; translated from Dutch.

Shakespeare Received on Wed Apr 02 2008 - 11:50:54 CDT