Win a copy of Five Lines of Code this week in the OO, Patterns, UML and Refactoring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

turn on / off db constraints -

 
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.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE 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?
 
author & internet detective
Posts: 40035
809
Eclipse IDE VI Editor Java
  • 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.
 
Ranch Hand
Posts: 893
Tomcat Server Java Ubuntu
  • 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.
 
Don't get me started about those stupid light bulbs.
    Bookmark Topic Watch Topic
  • New Topic