• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

turn on / off db constraints -

 
Sharon Adar
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
(java / mySql)

I�m trying to run some batch commands one after the other -
Yet since part of them may fail due to constraints failures �
I want to turn off the DB constraints, and turn it back on just
after the last batch is executed (in the same connection!!!).
Once the constraints are back � I want to see all the constraints failure.
(apparently � there�ll be none)

How can I turn the DB constraints off at the beginning of the batch execution,
And later turn it on and see the result and errors?

Thanks,
Sharon.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why not order your statements so they don't hit any of the constraints? That's the easier (and safer) route.

Off the top of my head I think you can disable constraints in MySQL with the code:
Or something like that. Not sure what you do about unique indices though.
 
Sharon Adar
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the real fast answer!
unfortunately, I can't order the updates before the execution...

Do you know any other way to do this disabling through Jdbc?
 
Sharon Adar
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your real fast answer!
unfortunately, I can't order the updates before the execution...

Do you know any other way to do this disabling through Jdbc?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sharon,
Some databases (like Oracle) store the constraints in a table. In those databases, disabling/enabling constraints can be done through SQL. I'm not sure how mySQL does it, but you might want to have a look if there are system tables.
 
Remko Strating
Ranch Hand
Posts: 893
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The Mysql table should use the InnoDB storage engine and you can use the following command.

SET FOREIGN_KEY_CHECKS=0 for switching off foreign keys
SET FOREIGN_KEY_CHECKS=1 for switching on foreign keys

If your using mysql version 5.0 I would write your statements in 1 stored procedure.
 
Sharon Adar
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've tried Remko suggestion, and wrote this simple main:



Surprisingly, executing "SET FOREIGN_KEY_CHECKS=1" succeeded, in spite of the illegal my_second_class_id 'pizza'! Those changes were written to the DB, and showed no errors or constraint failures!
I didn't expect this.
I hoped once the constraints are turned on, all the rows will be checked, and a sqlException with constraint failure will be thrown.
How can I see those errors once the constraints are back?

Thanks again.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic