Oracle Database Discussions

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.

My foreign key question regarding using a UNIQUE INDEX has been sorted -
1096544

I have a more fundamental problem now though 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'); 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); 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; 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...