RE: Referential Integrity Equivalence Question

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Mon, 30 Sep 2013 19:55:50 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD118764A1D_at_G6W2491.americas.hpqcorp.net>



"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)."

Actually it makes perfect sense. When you perform a where col_a = 'STRING' with most installations of SQL Server you do not have to worry about case. The query will find a hit if the data is stored as STRING, string, String, or any combination of mixed case letters.

-----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
Received on Mon Sep 30 2013 - 21:55:50 CEST

Original text of this message