ORA-02291: integrity constraint error

[Megha Singhal]

i am first fetching values from the table like following
String abc = "select LEAV_CODE from R_LEAVE where LEAV_DESC='"+NOL+"'";
here NOL is the variable in which i am fetching value from dropdown field
then i fetch the leave code in the variable nol like following
rs1 = st1.executeQuery(abc); while(rs1.next()) { nol=rs1.getInt("LEAV_CODE"); }
and if i am trying to print this code individually then it is giving right output but if i am trying insert this nol value in another table like following
String query = "insert into TRANS(SRNO,LEV_TYPE,FROM_DT,TO_DT) values (srno_gen.nextval,"+nol+",to_date('"+FROM+"','DD/MM/YYYY'),to_date('"+TO+"','DD/MM/YYYY'))"; int i = st2.executeUpdate(query);
then it is giving me following error

An Error Had occurred while accessing the database ORA-02291: integrity constraint (TRANS_R_LEAVE_FK1) violated - parent key not found

i don't understand why this is giving error as i am not inserting any value i am just fetching value
on following constraint it is giving me error
CONSTRAINT "TRANS_R_LEAVE_FK1" FOREIGN KEY ("LEV_TYPE") REFERENCES "R_LEAVE" ("LEAV_CODE") ENABLE )
so the above constraint should give problem when i am trying to insert something .

[Matthew Brown]

If you aren't trying to insert anything, what is the "insert into TRANS(...)" statement for?

[Megha Singhal]

Matthew Brown wrote:If you aren't trying to insert anything, what is the "insert into TRANS(...)" statement for?

no but i am inserting in the TRANS and the problem is coming in R_LEAVE which is showing that i am trying insert something in the R_LEAVE

[Martin Vashko]

Megha Singhal wrote:no but i am inserting in the TRANS and the problem is coming in R_LEAVE which is showing that i am trying insert something in the R_LEAVE

No.

It is saying that the value you're trying to insert into the column LEV_TYPE in table TRANS is not one of these defined in table R_LEAVE.

[Megha Singhal]

Martin Vajsar wrote:

Megha Singhal wrote:no but i am inserting in the TRANS and the problem is coming in R_LEAVE which is showing that i am trying insert something in the R_LEAVE

No.

It is saying that the value you're trying to insert into the column LEV_TYPE in table TRANS is not one of these defined in table R_LEAVE.

LEAV_CODE and LEAV_DESC are both the fields in the R_LEAVE and fetching the values from the LEAV_CODE , i am inserting values in the LEV_TYPE as LEAV_CODE and LEV_TYPE is the same.
actualy what is happening in my form the user is inserting LEAV_DESC but as the field LEV_TYPE is numeric so i have to select LEAV_CODE corresponding LEAV_DESC from R_LEAVE then inserting in the TRANS table in the field LEV_TYPE.

[Martin Vashko]

Post more of your code then. The value for the LEV_TYPE column has got mangled somewhere on its way in.

You should learn to use PerparedStatement. You might start with a good tutorial. There are several reasons to do so, including security, performance and correctness of your code, as described here.

[Megha Singhal]

Martin Vajsar wrote:Post more of your code then. The value for the LEV_TYPE column has got mangled somewhere on its way in.

You should learn to use PerparedStatement. You might start with a good tutorial. There are several reasons to do so, including security, performance and correctness of your code, as described here.

what i have to post now all the queries i already posted and rest is the java code and that i can't post in this forum.

[Martin Vashko]

Well, if all was done as you have described it, then it would work. Since it doesn't, there is probably a bug somewhere in your code; if you cannot post the code (which I understand), we cannot help you spot the bug directly.

In this case, you can either debug your code using a debugger, or try to output values of key variables into a log so that you see what is happening to the variable which you're then inserting into the TRANS.LEV_TYPE column. You could even write the complete SQL statement you've constructed to a log (since you're not using PreparedStatement, but that is actually a bad thing too as I've already mentioned).

[Megha Singhal]

Martin Vajsar wrote:Well, if all was done as you have described it, then it would work. Since it doesn't, there is probably a bug somewhere in your code; if you cannot post the code (which I understand), we cannot help you spot the bug directly.

In this case, you can either debug your code using a debugger, or try to output values of key variables into a log so that you see what is happening to the variable which you're then inserting into the TRANS.LEV_TYPE column. You could even write the complete SQL statement you've constructed to a log (since you're not using PreparedStatement, but that is actually a bad thing too as I've already mentioned).

ok then i try it to do through prepared statement, if then also my doesn't solve then i'll get back to you.

[Martin Vashko]

Switching to PreparedStatement alone probably won't resolve this error. It will just make your code protected from SQL injection attacks. It is possible, however, that while making changes needed to this you'll spot or incidentally fix the other bug.

[Fatih Keles]

Hi Megha,

What is the value of "nol" variable before insert statement? It can be null and not populated by your first query. As Martin said you should debug your code, it will reveal the error.