Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: integrity constraint violated error when there should be none

Re: integrity constraint violated error when there should be none

From: Davis <davis_at_sqlmagic.com>
Date: 18 May 2004 15:48:28 -0700
Message-ID: <80de4f.0405181448.280fdc21@posting.google.com>


schepuri_at_oatsystems.com (schepuri) wrote in message news:<44f3bec.0405170332.7fcf8a17_at_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

I've seen cases where the error message indicates that an integrity constraint has been violated but in fact a unique index is the problem. Could that be the situation you're encountering? This would be very data-dependent which could explain why you get different results sometimes. Received on Tue May 18 2004 - 17:48:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US