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

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'.

Cheers.

Carlos. Received on Wed Apr 02 2008 - 10:21:09 CDT