dependency error in java : problem in solving child record found vs parent key not found

[bilal haider]

Hi All,
Hope all are doing fine. Through java when i was doing

update Department
set id =10
where id =100;

update employee
set d_id=10
where d_id =100;

I get following errors:

ORA-02292: integrity constraint (MIS.DEPT_FK) violated - child record found
ORA-02291: integrity constraint (MIS_EMP_FK) violated - parent key not found

I issued following comand

set constraints SERVICE_DETAIL_2_FK deferred

but the constraint is not deferrable.

Any workable solution to achieve the desired result?

[Martin Vashko]

To be able to defer Oracle constraints, the constraint must be created as DEFERRABLE, which it apparently wasn't. You could drop the constraint and recreate it as deferrable, then the SET DEFERRED command will work.

However, that you need to do this indicates your data model contains mutable primary keys. This is a mistake, as was in depth discussed here: https://coderanch.com/t/569132/JDBC/java/Update-value-primary-key-impossible

Also, let me say that there is probably a bug in Oracle which affects deferrable foreign keys. We identified a broken FK in our database on columns which were manipulated in the deferred mode. It seems that the bug only surfaces if the affected rows are accessed/modified from another session at the same time (we were not able to reproduce the bug yet, just saw the violated FK constraint in the database).

[chris webster]

You can disable the constraints temporarily, perform your changes, then re-enable the constraints, but remember to check for errors.

But as Martin says, you should not be changing your primary key anyway.

[bilal haider]

@ Martin : I disabled the constraint and altered it as deffereable . then executed the

update Department
set id =10
where id =100;

update employee
set d_id=10
where d_id =100;

and got success. after that i enabled the constraint. But my question is that the time interval for which i deffered the constraint, someone else can access the db and do invalid operations might be performed voilating constraint.

I want this in java

Deffered statment for this session only
Execute the statments
Enable constraint

How can I do that, Is there any workaround in java. I googled but unable to solve this.
@ Chris : I want to do this in java and for the current session only, so that if some one is accessing the db at the time onstraints are disabled by my procedure , constraints must be checked for that user.

[Martin Vashko]

Well, my first advice always will be: don't do that. The need for a cascaded update is a symptom of bad database design. I described in the thread I posted earlier how I got burned with mutable primary keys.

There are two ways to temporarily lift constraints in Oracle (as far as I know). It seems you're not differentiating these two ways properly:

1) Disable constraint. This can be done via the ALTER command, but it is a DDL operation and affects all sessions. This is not what you want.

2) Defer constraint. This is done via the SET command and affects only the current session. The constraint remain in effects for other sessions, unless they also defer it, and it gets enforced again upon commit or when setting the constrain back to IMMEDIATE. So this is what you want, at least in theory.

There are two caveats with deferred constraints: the constraint must be created as DEFERRABLE. Once created, it cannot be altered to be DEFERRABLE. So until you drop and recreate the constraint as deferrable, you cannot defer that constraint.

Secondly, there is probably a bug in Oracle in handling deferred constraints. We noticed rows violating FK constraint in customer's database in tables which are handled with deferred mode sometimes. I tried to convince the customer's DBAs to file this with Oracle support, but they didn't. However, a web search confirmed we're not the only one who run into such an issue.

So, to wrap it up: my advice is not to update primary keys. Try to have the database model (which is seriously flawed) to be repaired.

[bilal haider]

Yes prior to your current post i was not clear in the solution and i was mixing the two techniques, Now i am more clear with the deffer constraint technique because it lasts for the current session only. This is the only way i can do what i want to do, secondly i am not allowed to alter this bad db design :-(

Please suggest me how can i do this in java ? any example because i am stuck in the issue for five days and already late.

And thank you for your time and fruitfull response,

[Martin Vashko]

1) You'll have to drop and recreate the foreign key constraint as deferrable, if you have not done that yet. If the constraint was not explicitly named before, assign it a name; otherwise it will have some system generated name which can change upon export and import and is generally less maintainable.

2) You'll issue an SQL statement from Java (preferably PreparedStatement, even though it doesn't have any parameters)
set constraints all deferred orset constraint your_constraint_name deferred
After the update you can set the constraint(s) back to immediate (at which time they would be checked); if you don't, the check will be done at commit.

The exact syntax of the above commands can be found here.