How to handle parent key constraints in jdbc transactions?

Question

Hi, I have 2 tables named T1 and T2. Where T1 is parent and T2 is child. The scenario is, I started a jdbc transaction and then insert a row in T1 and then try to insert a row in T2. Inserting row in T2 gies me "Integrity Constraint-Parent key not found" exception.

How i handle this scenario ?

 Connection con;
try{
  con = ConnectionPool.getConnection();
  con.setAutoCommit(false);
  int T1Id = getNewId("T1"); // from sequence;
  int T2Id = getNewId("T2"); // from sequence;
  Insert in to table T1(t1Id,tName) values (T1Id,'A')
  Insert in to table T2(t2Id, t1Id,tName) values (T2Id,T1Id,'A')//Here, Exception raises

  con.commit();

 }catch(Exception e){
    try {con.rollback();} catch (SQLException e) {}

 }finally{
     try {con.setAutoCommit(true);} catch (SQLException e) {}
     ConnectionPool.returnConnection(con);
}

Using JDBC API, struts1.2, Oracle10 G Database

Answer 1

You are probably doing something wrong. If both inserts are within the same transaction what you've just mentioned can't happen. Please share some code and more information (DB server, table structures) to see if we can help you.

Answer 2

You need a three step process:

1. INSERT row into parent
2. SELECT the generated key from the parent
3. Use the generated key and the child information to INSERT into the child

It should be a single unit of work, so make it transactional.

It's impossible to tell from your pseudo code. It'd also be helpful to know whether or not you're using auto generated keys.

I'm guessing that the primary key you're assuming for T1 doesn't actually appear. If T2 says the foreign key cannot be null or is required, and it doesn't appear in T1, then the RDBMS system should complain and throw an exception.