Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2014
    Posts
    9

    Unanswered: case-insensitive unique constraint

    Is it possible to do 'case-insensitive unique constraint' on possible case-sensitive table?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by icegood View Post
    Is it possible to do 'case-insensitive unique constraint' on possible case-sensitive table?
    that is default behavior by Oracle
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by anacedent View Post
    that is default behavior by Oracle
    Maybe I'm misunderstanding, but I don't think that that is true:
    Code:
    SQL> drop table a;
    
    Table dropped.
    
    SQL> create table a (col1 varchar2(10), col2 number);
    
    Table created.
    
    SQL> alter table a add constraint unk_col1 unique(col1);
    
    Table altered.
    
    SQL>
    SQL> insert into a values('A',1);
    
    1 row created.
    
    SQL> insert into a values('a',1);
    
    1 row created.
    
    SQL> insert into a values('A',1);
    insert into a values('A',1)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (RO_1065206.UNK_COL1) violated
    @OP
    Create a function based index to do this:
    Code:
    SQL> drop table a;
    
    Table dropped.
    
    SQL> create table a (col1 varchar2(10), col2 number);
    
    Table created.
    
    SQL> create unique index unk_idx on a(upper(col1));
    
    Index created.
    
    SQL>
    SQL> insert into a values('A',1);
    
    1 row created.
    
    SQL> insert into a values('a',1);
    insert into a values('a',1)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (RO_1065206.UNK_IDX) violated
    Last edited by pablolee; 07-15-14 at 11:52.

Posting Permissions

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