title: Oracle Foreign Key Issues with Multi-Table Inserts and Blobs
body: <p>We have a single table that we want to break up into a tree of tables based upon a particular source column. I wanted to try using a multi-column insert, but it seems that if I insert a blob into a sub table, I wind up with a foreign key constraint violation. </p>
  
  <p>I don't think this violates the <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2095116" rel="nofollow">rules about multi-table inserts</a> but I could be wrong...</p>
  
  <p>I am hoping that someone could point me to some more in-depth resources around what is actually going on here, so that I can feel confident that whatever solution will work as part of a liquibase changeset on Oracle databases 9i -> 11g.</p>
  
  <h1>Hopefully Simplified Scenario</h1>
  
  <pre><code>CREATE TABLE source (
      pk NUMBER NOT NULL PRIMARY KEY,
      type VARCHAR2(20) NOT NULL,
      content VARCHAR2(20) NOT NULL
  );
  
  INSERT INTO source (pk,type,content) values (1,'two','n/a');
  INSERT INTO source (pk,type,content) values (2,'one','Content');
  
  CREATE TABLE dest (
      pk NUMBER NOT NULL PRIMARY KEY,
      type VARCHAR2(20) NOT NULL
  );
  
  
  CREATE TABLE dest_one  (
      pkfk NUMBER NOT NULL PRIMARY KEY,
      data BLOB NOT NULL,
      CONSTRAINT XFK1DEST_ONE FOREIGN KEY (pkfk) REFERENCES dest (pk)
  );
  
  
  CREATE TABLE dest_two  (
      pkfk NUMBER NOT NULL PRIMARY KEY,
      CONSTRAINT XFK1DEST_TWO FOREIGN KEY (pkfk) REFERENCES dest (pk)
   );</code></pre>
  
  <p>Source contains our original data. dest will be our parent table, with children dest_one and dest_two (which will contain information on things of type 'one' or 'two' respectively). Things of type one have content, but things of type two do not. </p>
  
  <h2>The Failed Attempt</h2>
  
  <pre><code>INSERT ALL
  WHEN 1=1 THEN INTO dest (pk,type) VALUES (pk,type)
  WHEN type='one' THEN INTO dest_one (pkfk,data) VALUES (pk,content)
  WHEN type='two' THEN INTO dest_two (pkfk) VALUES (pk)
  SELECT pk,type,utl_raw.cast_to_raw(content) as content from source where type in ('one','two');
  </code></pre>
  
  <p>As previously mentioned, I wound up with a foreign key constraint violation here. To further illustrate that the blob was the issue I tried two seperate similar queries (below) realizing the one without the blob insert worked, but with the blob insert failed.</p>
  
  <pre><code>INSERT ALL
  WHEN 1=1 THEN INTO dest (pk,type) VALUES (pk,type)
  WHEN type='two' THEN INTO dest_two (pkfk) VALUES (pk)
  SELECT pk,type,utl_raw.cast_to_raw(content) as content from source where type = 'two';
  /* Successful */
  
  INSERT ALL
  WHEN 1=1 THEN INTO dest (pk,type) VALUES (pk,type)
  WHEN type='one' THEN INTO dest_one (pkfk,data) VALUES (pk,content)
  SELECT pk,type,utl_raw.cast_to_raw(content) as content from source where type = 'one';
  /* ORA-02291: integrity constraint violated, no parent key */</code></pre>
  
  <h2>Solution 1 - Traditional Inserts</h2>
  
  <pre><code>INSERT INTO dest (pk,type) SELECT pk,type from source where type in ('one','two');
  INSERT INTO dest_two (pkfk) SELECT pk from source where type = 'two';
  INSERT INTO dest_one (pkfk,data) SELECT pk,utl_raw.cast_to_raw(content) from source where type = 'one';</code></pre>
  
  <p>One option I am considering is going back to multiple seperate insert statements, but unlike how I have stated them here, I'm concerned that I'll have to make sure I write my sub-table inserts to only attempt to insert those rows present in parent dest table... I need to do more research on how Liquibase handles multiple sql statements in the same changeset.</p>
  
  <h2>Solution 2 - Temporarily disabling foreign key constraints</h2>
  
  <p><pre><code>ALTER TABLE dest_one DISABLE CONSTRAINT XFK1DEST_ONE;</p>
  
  <p>INSERT ALL
  WHEN 1=1 THEN INTO dest (pk,type) VALUES (pk,type)
  WHEN type='one' THEN INTO dest_one (pkfk,data) VALUES (pk,content)
  WHEN type='two' THEN INTO dest_two (pkfk) VALUES (pk)
  SELECT pk,type,utl_raw.cast_to_raw(content) as content from source where type in ('one','two');</p>
  
  <p>ALTER TABLE dest_one ENABLE CONSTRAINT XFK1DEST_ONE;</pre></code></p>
  
  <p>This is the solution I'm leaning toward. While disabling the foreign key on my blob table seems to make it work in my test environment (10g - 10.2.0.1.0), I'm not sure if I should also be disabling the foreign key on the non-blob table as well (due to how 9i, 11g, or other versions of 10g may behave). Any resources here too would be appreciated. </p>
  
  <p>Thanks a bunch! </p>