"Parent Key Not Found" although it exists (within the TX)

Question

I just observed a strange behaviour (of course Oracle is probably supposed to behave this way, but it didn't fit in my world view yet):

I try to insert two rows into a parent and a child table, both within the same transaction:

INSERT INTO V_Parent (ID, Name) VALUES (777, 'Hello World');
INSERT INTO T_Child (ParentID, Name) VALUES (777, 'Foo Bar');

The Child table has a (ParentID) references Parent.ID foreign key constraint.

On the second statement Oracle fails with the error message "Parent key not found."

If I disable the FK constraint, it works. I have asserted that the ParentID and the Parent.ID match, and I am 100% sure that the first line is executed successfully before the second one. Further, I have tried to commit each statement, which worked fine.

However, as the prefixes in my code example suggest, the first INSERT is actually done on a view of the parent table. The reason is that I use NHibernate and the mapping uses the view in background (which didn't cause any problems until today).

Q1: Could it be that inserting on a view is deferred by Oracle so that the second statement fails?

Q2: How can I remedy this problem best?

• Do I need to define INSTEAD OF triggers on the views?
• Can I change a setting on the VIEW definition?
• Can I change a setting on the FOREIGN KEY definition?
• (I must not bend the hibernate mapping to the original table: It's a demand to use the views so changes and/or security issues can be hidden behind the views)

Details: C# WinForms Application - NHibernate - Oracle 10.2 - T_Child: Sooner or later I will use a view for that table, too, it's simply not defined yet.

---

Edit: More Details according to the comments:

• The ID is assigned by NHibernate using an Oracle sequence (<generator class="sequence">), and is part of the INSERT statement as in my example. I also verified that the resulting ID in the table row matches the one NHibernate saved in the mapped object.
• The view is defined as a SELECT that JOINS some fields of other tables. However, on insert/update I only change the fields belonging to the main table ("T_PARENT"), and that normally works fine.
• The current foreign key constraint is not deferrable, but that shouldn't have any effect because the parent statement is executed before the child statement. *)

*) Hmm... let me think: Since I use an NHibernate session for submitting the SQL queries, could it be that NHibernate executes them in a different order than I told it to?

I'll investigate on that. => It seems so, see my own answer.

This is how the actual code looks like:

ISession session = this.DAOFactory.NHibernateHelper.SessionFactory.OpenSession();
ITransaction tx = session.BeginTransaction();

try
{
    // parent.ID == 0
    session.SaveOrUpdate(parent);
    // parent.ID == 777 (for example)

    ISQLQuery query = session.CreateSQLQuery(
        "INSERT INTO T_CHILD (PARENT_ID, NAME) VALUES (:parentId, :name)");
    query.SetDecimal("parentId", parent.ID);
    query.SetDecimal("name", "Foo Bar");

    query.ExecuteUpdate(); // Fails with ORA-Exception

    tx.Commit();
}
catch (Exception)
{
    tx.Rollback();
    throw;
}
finally
{
    session.Close();
}

Answer 1

Hi chiccodoro,

You don't need to define an INSTEAD OF trigger if the view is already updateable. Inserting in a view that has a simple relation with its base table will behave as inserting in the base table -- consider:

SQL> CREATE TABLE t (a NUMBER, b NUMBER, c NUMBER);

Table created

SQL> CREATE VIEW v AS SELECT a, b FROM t;

View created

SQL> INSERT INTO v VALUES (1,2);

1 row inserted

SQL> SELECT * FROM t;

         A          B          C
---------- ---------- ----------
         1          2 

For your insert problem, you probably have a BEFORE INSERT trigger on the base table (with the id colomn filled by a sequence).

Answer 2

I've got it.

As stated in the update to my question, it seems that the NHibernate session mixes the order of the SQL statements. To remedy this, I added the following line of code:

session.SaveOrUpdate(parent);
session.Flush();
// (...)
query.ExecuteUpdate();