Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    8

    Question Unanswered: Check Uniqueness regardless os case

    Hello,

    I need to check before an update or insert if the value of a varchar column is unique regardless of case (ORACLE and oracle are not allowed).
    For some reason usage of a function index is not possible.

    For the insert it is possible to use a BEFORE INSERT trigger, but for UPDATE a BEFORE UPDATE trigger is not possible bacause it would use a select query on the table which will be updated (Error ora-04091).

    Does anyone has an idea how to solve this request ?

    Thanks
    Dirk

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The only way I could think of (if you really, really cannot use a function based index), is to change the collation for the database to a case-insensitive collation. But that would affect all tables then, and I'm sure you wouldn't want that.

    So, why can you not use a function based index?

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If you must use a trigger, you can avoid the "mutating table" error by following this method from Tom Kyte (Case 1 should suffice).

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You say that you can't put a function based index on the table. However, you are free to put triggers. WHY can't you simply type

    CREATE UNIQUE INDEX MY_TABLE_U1 ON MY_TABLE(UPPER(MY_COLUMN));
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    8
    Thanks for your answers.

    I cant't use a function based unque index because the table is used by an application with a dymanic database access layer. This application reads out unique index information from the system catalog and uses it for building transactions and queries. Reading out index information throws errors because the application cannot relate the generated index column name to a "real" column of the table.

    It seems that there is no simple solution using Oracle mechanism, so I requested a change in application.

    Thanks Dirk

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by drosemeyer
    I cant't use a function based unque index because the table is used by an application with a dymanic database access layer. This application reads out unique index information from the system catalog and uses it for building transactions and queries. Reading out index information throws errors because the application cannot relate the generated index column name to a "real" column of the table.
    Your "dymanic" was a typo for "dynamic", buy how appropriate to this kind of system that's too "clever" for its own (or your) good!

  7. #7
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Can you fake the app out by building the index in a different schema? (It would depend on which views the app is looking at to build it's own dictionary...)

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    Can you create another column which has a unique index set on it, and then push the UPPER or LOWER equivalent of the mixed-case column into it:

    Code:
    create table dup_col (f1 varchar2(10), f2 varchar2(10));
    
    create unique index dup_col_ux on dup_col(f2);
    
    create or replace trigger dup_col_bt
    before insert or update
    on dup_col
    for each row
    begin
      :NEW.F2 := UPPER(:NEW.F1);
    end dup_col_bt;
    
    insert into dup_col (f1) values ('chuck');
    1 row inserted 
    
    insert into dup_col (f1) values ('CHUCK');
    ORA-00001: unique constraint (FORBESC.DUP_COL_UX) violated

Posting Permissions

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