Integrity Constraint violated - Child record found

I tried to update a record like this:

update work_order set wo_id = 2 where wo_id = 1;

As I have a message integrity constraint violated - child record found, I tried to modify foreign key constraint by doing this;

Alter table work_order modify constraint constraint_name disable;

Commit successful, but it doesn't save the change that I made.
lium1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Why would you disable a constraint?  They are there to ensure data integrity.  If you disable one to allow you to do something they prevented, you no longer have data integrity.

Undo the update and re-enable the constraint.  It was there for a reason.

If you need to do that update, fix the child records first.  then do the update.  Otherwise you'll have orphaned wo_id=1 children.

All that said:
If the rows say they were updated and the update was committed, then it was done.  trust Oracle.
lium1Author Commented:
I have started out the wrong number for wo_id auto generated by my sequence. Since the starting number for wo_id was wrong, I need to re-input the wo_id by updating it. That is where I run into integrity constraint for child record. So, I disable all the constraints related to work_order table, then update wo_id. it said commit successful, but when I check the data in the table it still not changed!
slightwv (䄆 Netminder) Commented:
Updating rows isn't re-inputting them.  I would look at leaving the integrity constraints in place, creating temp tables to hold the 'bad' rows, updating those, deleting from the main tables and re-inserting with correct values from the temp tables.

Then you can ensure data integrity among ALL tables involved.

When you disable constraints and update rows you get into the situation in your previous question (which is still open by the way):
http://www.experts-exchange.com/questions/28733020/Integrity-constraint-parent-key-not-found.html

If the update committed and rows were updated then the update happened.  Either you aren't looking at the correct data after the update or you updated the wrong rows.

Oracle wouldn't be in business if committed updates didn't actually happen.
johnsoneSenior Oracle DBACommented:
No need to use temporary tables.  Defer the constraint.  Once you do that it will not check the constraint until it commit.  That way you can update the parent record without violating the constraint.  You just need to update the child record in the same transaction.

This is exactly the situation that deferred constraints were made for.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.