RE: Referential Integrity Equivalence Question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 30 Sep 2013 14:29:22 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DB9549_at_exmbx05.thus.corp>

Has anyone suggested virtual columns - if the version is appropriate.

Unless you've already got corrupted data then drop the existing PK and FK, and create virtual columns on upper(parent_value) and upper(child_value), then declare primary key and foreign key on the virtual columns.

I doubt if anyone has done an exhaustive test on why this may cause odd side effects, but in the trivial case it seems to work okay.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Tefft, Michael J [Michael.J.Tefft_at_snapon.com] Sent: 30 September 2013 14:48
To: oratune_at_yahoo.com; christopherdtaylor1994_at_gmail.com; oracle-l_at_freelists.org Subject: RE: Referential Integrity Equivalence Question

It's worse than that.
The parent key presumably may also be uppercase, lowercase, or some mixture. So, to make this work, you will need to either: - Scrub the parent to force all of its 'keys' to be a consistent case, and likewise scrub the child rows' foreign-key columns. By doing this, you have either lost the original case of the data, or you have introduced a new column for the 'scrubbed' value. - Look up the correct value in the parent - such as SELECT PK_COL FROM PARENT_TABLE WHERE UPPER(PK_COL) = UPPER(CHILD_TABLE.FK_COL) - and populate that value instead of the original for the FK reference. You again have to decide whether to keep (in a separate column) the original mystery-case FK value, or not.

Not all shortcuts lead to Grandma's house...

Mike Tefft

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Fitzjarrell Sent: Friday, September 27, 2013 6:17 PM To: christopherdtaylor1994_at_gmail.com; oracle-l_at_freelists.org Subject: Re: Referential Integrity Equivalence Question

As mentioned in a previous reply SQL Server can be configured to be case-insensitive (which makes no sense to me why anyone would want to do that).

I suppsoe you can disable the FK constraint, load the data, then run an update on the child table using upper() against the foreign key column to get it to match the parent records where the key columns don't match with respect to case:

update child_table c
set c.fk_col = upper(c.fk_col)
where c.fk_col = (select lower(pk_col) from parent_table where pk_col = upper(c.fk_col));

I have tested this and it does work:

SQL> create table parent_table(
  2 pk_col varchar2(10),
  3 parent_data varchar2(40),
  4 constraint parent_table_pk primary key (pk_col));

Table created.

SQL>
SQL> create table child_table(
  2 fk_col varchar2(10),
  3 child_data varchar2(40));

Table created.

SQL>
SQL> begin

  2          for i in 1..100 loop
  3                  insert into parent_table
  4                  values('V'||i, 'Parent record '||i);
  5          end loop;
  6          for i in 1..100 loop
  7                  for j in 1..10 loop
  8                  insert into child_table
  9                  values('v'||i, 'Child record '||i);
 10                  end loop;
 11          end loop;
 12          commit;

 13 end;
 14 /

PL/SQL procedure successfully completed.

SQL>
SQL> alter table child_table add constraint parent_table_fk foreign
SQL> key(fk_col) references parent_table(pk_col);
alter table child_table add constraint parent_table_fk foreign key(fk_col) references parent_table(pk_col)
                                       * ERROR at line 1:

ORA-02298: cannot validate (GRIBNAUT.PARENT_TABLE_FK) - parent keys not found SQL>
SQL> update child_table c
  2 set c.fk_col = upper(c.fk_col)
  3 where c.fk_col = (select lower(pk_col) from parent_table where pk_col = upper(c.fk_col));

1000 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> alter table child_table add constraint parent_table_fk foreign
SQL> key(fk_col) references parent_table(pk_col);

Table altered.

SQL>
I know it's a work-around but it is a suggestion. The other option is to get the feed to provide the data in the 'correct' case relative to the primary key values. David Fitzjarrell

---

 From: Chris Taylor <christopherdtaylor1994_at_gmail.com> To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Friday, September 27, 2013 3:31 PM Subject: Referential Integrity Equivalence Question

We get a data feed from SQLServer to load into some oracle tables. On the Oracle side, the FK constraint is failing after the load.

When reviewing the data that was sent, I discovered that:

(parent data value)

PARENT VAL = V48
(child data value)

CHILD VAL = v48

(FK fails because these aren't the same in Oracle)

Someone in my org mentioned that SQLServer is case insensitive and that's why it happened.

That doesn't seem possible to me (but perhaps it is) because "v" and "V" aren't the same at the binary level - they have different code values on the code page. (Unless the MSWIN Codepage in use on the SQL Server has them the same?)

Am I mistaken on that?

Thanks
Chris

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 30 2013 - 16:29:22 CEST