Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Unanswered: CASE INSENSITIVITY and FOREIGN KEY CONSTRAINTS

    Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE 11.2.0.1.0 Production
    TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production

    Hello all.

    I have a problem in that I cannot see how to get FOREIGN KEY references working in a case insensitive set-up.

    Lets start with a 'normal' case sensitive set-up.

    I have my 2 tables -
    CREATE TABLE tparent(
    pid NVARCHAR2(10) NOT NULL,
    pid2 NVARCHAR2(10) NOT NULL);

    CREATE TABLE tchild(
    cid NVARCHAR2(10) NOT NULL,
    cid2 NVARCHAR2(10) NOT NULL);

    I create a PRIMARY KEY on tparent -
    ALTER TABLE tparent ADD CONSTRAINT pk_tparent PRIMARY KEY (pid);

    I create a FOREIGN KEY on tchild -
    ALTER TABLE tchild ADD CONSTRAINT fk_tc_to_pt FOREIGN KEY (cid) REFERENCES tparent(pid);

    All OK - I can only insert a record into tchild is it exists in tparent.
    i.e.
    INSERT INTO tparent VALUES ('aaa', 'aaa');
    INSERT INTO tchild VALUES ('aaa', 'aaa');
    INSERT INTO tchild VALUES ('xxx', 'xxx');
    > SQL Error: ORA-02291: integrity constraint (TRAINDB.FK_TC_TO_PT) violated - parent key not found

    Now lets go CASE INSENSITIVE.
    I set my NLS_ values as thus -
    ALTER SESSION SET NLS_SORT=GENERIC_M_CI;
    ALTER SESSION SET NLS_COMP=LINGUISTIC;

    Re-set everything up (just drop everything...) -
    DROP TABLE tchild;
    DROP TABLE tparent;

    CREATE TABLE tparent(
    pid NVARCHAR2(10) NOT NULL,
    pid2 NVARCHAR2(10) NOT NULL);

    CREATE TABLE tchild(
    cid NVARCHAR2(10) NOT NULL,
    cid2 NVARCHAR2(10) NOT NULL);

    I create a UNIQUE INDEX on tparent (Cannot use a PK as PK's are case sensitive, instead use an index with UPPER )
    CREATE UNIQUE INDEX ui_tparent ON tparent(UPPER(pid));

    I create a FOREIGN KEY on tchild -
    ALTER TABLE tchild ADD CONSTRAINT fk_tc_to_pt FOREIGN KEY (cid) REFERENCES tparent(pid);
    > 02270. 00000 - "no matching unique or primary key for this column-list"

    Ok, this error is expected as even though we have a UNIQUE INDEX on tparent we still need to create a UNIQUE CONSTRAINT against ui_tparent - and here is the problem.

    ALTER TABLE tparent ADD CONSTRAINT uc_tparent UNIQUE (pid) USING INDEX ui_tparent;
    > 14196. 00000 - "Specified index cannot be used to enforce the constraint."

    It seems because I used UPPER in the ui_tparent index I cannot reference it in a UNIQUE CONSTRAINT.

    UPPER(pid) or NLSSORT(pid, 'NLS_SORT=GENERIC_M_CI') against the index are the 'recommended' Oracle way to do case insensitivity but it seems that I have hit a brick wall here regarding FOREIGN KEY references...

    I have tried numerous combinations etc but have found no solution.

    Is this the end of my foray into case insensitivity in Oracle - I hope not...

    Any of you Oracle wizards out there to give me a hand...

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oracle constraints are case sensitive, you can't get around that fact.

    As I just replied here on forums.oracle.com:
    There is no reason why the user should have to enter the customer ID in the correct case; you just have to convert it to the correct case before saving it to the database. This is commonly done using a trigger like this:

    Code:
    create trigger mytable_trg
    before insert or update on mytable
    for each row
    begin
       :new.customer_id := upper(:new.customer_id);
    end;

  3. #3
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    See also here...

    Yes, of course a trigger would be the solution and I have already done as you suggest under my testing plan, but that is not what I was trying to convey.

    Currently, through my investigations and the replies to my postings, it seems the only solution for a ‘case insensitive’ solution under Oracle is to UPPERCASE all our PRIMARY KEY/FOREIGN KEY values so that referential integrity can be enforced.

    To a degree, this makes a bit of a mockery of a case insensitive system when you have to store the PRIMARY values in UPPER CASE (lower case…).

    I was looking for a solution that would not require this but it seems there are none*, and the UPPER CASE solution is the best available.

    * A virtual column solution is also based on conversion to UPPERCASE. The main cause for concern here though is the need to shift the PRIMARY KEY(s) to the virtual column(s) which would require a significant amount of schema/SQL/code reference changes in our software and so (subject to more investigation) is a bit of a none-starter.

    More comments welcome.

    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
  •