Make a UNIQUE constraint case insensitive
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Make a UNIQUE constraint case insensitive

  1. #1
    Join Date
    Mar 2001
    Posts
    109
    Does any body know how to make a UNIQUE constraint case insensitive.

    For example, I create a table like:

    create table test (str varchar2(10) unique);

    insert into test values ('abc');
    insert into test values ('Abc');

    I hope that the second insert above would suffer a unique constraint violated error.

    Thanks for any help.
    zm

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    function based index ??
    svk

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by svk
     function based index??
    Function based index isn't UNIQUE constraint.
    This is only index.

    May be this (FBI) will works, but I dont' sure.

    create unique index xxx on test(upper(str)) ....;

    should test.

  4. #4
    Join Date
    Jul 2000
    Posts
    521
    that is what i meant.....
    svk

  5. #5
    Join Date
    Oct 2001
    Posts
    83
    Hello,

    If you are using 8i Enterprise Edition, you can opt for the solution proposed by Shestakov... If you are using Standard Edition or an Oracle version other than 8i (or 9i), you can opt for a solution using a trigger...

    Hope this helps

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width