integrity constraint violated error when there should be none

From: schepuri <schepuri_at_oatsystems.com>
Date: 17 May 2004 04:32:44 -0700
Message-ID: <44f3bec.0405170332.7fcf8a17@posting.google.com>

Hi,

I am facing a weird problem inserting records. I get the following exception:
java.sql.SQLException: ORA-02291: integrity constraint (SW_ADMIN.SYS_C0025227) violated - parent key not found error.

I am using Oracle 8.1.7.3 and ojdbc14_g JDBC Driver provided with Oracle 10. (JDK 1.4.1)

This error should not be happening as there aer no integrity constraints being violated. This error is not consistent (in the sense simillar inserts are working at few places but are not working at few other places). ALSO this error is not occuring when we use a third party JDBC Driver (DataDirect).

the scenario is

---

CREATE TABLE A (
   a_column1 INTEGER,
   a_column2 VARCHAR(10),
   PRIMARY KEY (a_column1)
);

CREATE TABLE B (
   b_column1 INTEGER,
   b_column2 VARCHAR(10),
   fk_a_column1 INTEGER,
   PRIMARY KEY (b_column1),
   FOREIGN KEY (fk_a_column1) REFERENCES A(a_column1) );

CREATE SEQUENCE A_seq START WITH 100;
CREATE SEQUENCE B_seq START WITH 200;

CREATE OR REPLACE TRIGGER t_A BEFORE INSERT ON A FOR EACH ROW BEGIN IF :NEW.a_column1 IS NULL THEN SELECT A_seq.NEXTVAL INTO :NEW.a_column1 FROM dual; END IF; END;

CREATE OR REPLACE TRIGGER t_B BEFORE INSERT ON B FOR EACH ROW BEGIN IF :NEW.b_column1 IS NULL THEN SELECT B_seq.NEXTVAL INTO :NEW.b_column1 FROM dual; END IF; END;

---

PreparedStatement ps = conn.prepareStatement("INSERT INTO A(a_column2) VALUES(?)");
ps.setString("A_TEST");
ps.executeUpdate();

PreparedStatement ps2 = conn.prepareStatement("SELECT a_column1 FROM A WHERE a_column2=?");
ps2.setString("A_TEST");
ResultSet rs = ps2.executeQuery();
long a_pk;
if(rs.next()) {

   a_pk = rs.getLong(1);
}

PreparedStatement ps3 = conn.prepareStatement("INSERT INTO B(b_column2, fk_a_column1) VALUES(?,?)"); ps3.setString("B_TEST");
ps3.setLong(a_pk);
ps.executeUpdate();

---


*****

The above code fails for the 2nd Insert statement for
ps3.setLong(a_pk);
With error ORA-02291: integrity constraint


*****

*****

BUT if we changes it to
ps3.setString("" + a_pk);
IT works fine.


*****



With the datadirect JDBC Driver we dont need the above modification IT
works fine.

ONLY With the ojdbc driver that comes along with Oracle9i we see this
problem.

The most weired part is it is always not the case, the exact similar
code works fine for some tables but doesn't work for few other tables.

Can someone let me know what might be the problem and what would be a
fix for this.

Thanks in advance,
Shambaiah

Received on Mon May 17 2004 - 06:32:44 CDT