wood burning stoves 2.0*
The moose likes Java in General and the fly likes Importing data into a database with a complex schema using Java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "Importing data into a database with a complex schema using Java" Watch "Importing data into a database with a complex schema using Java" New topic
Author

Importing data into a database with a complex schema using Java

M Burke
Ranch Hand

Joined: Jun 25, 2004
Posts: 383
I am making a utility to read a database and generate SQL INSERT statements for each row in every table. I want to be able to move data with a script from one DB to a DB at another job site. I am currently using MS SQL 2000.

Problem is...the schema has foreign key (FK) constraints. I get the meta data from the database and get a list of the tables. But it gives me the list in alphabetical order. It has no concept of FK constraints. So when I go to run the script, I get FK violations because some rows holding the FK have not yet been created.

Any suggestions?
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3697
    
    5

One solution might be to set all foreign key constraints to null (or some record that represents null if the column is set as NOT NULL) then after the data has been inserted, go back and use update commands to set all fields properly.

Which database are you using? Many provide internal methods for dumping entire databases into SQL statements.

Another solution is read all of the data from the database and load it into Hashtables. Then once all Hashtable references have been resolved, insert the data such that if you encounter a reference that has not been inserted, insert it before its parent.

There's a number of other techniques that will work although trying to do this dynamically for an entire database can be difficult, *especially* if records cross reference each other between two different tables.


My Blog: Down Home Country Coding with Scott Selikoff
Mathias Nilsson
Ranch Hand

Joined: Aug 21, 2004
Posts: 367
Remove the constraints before insert and add them again

-- disable the myconstraint constraint in the mytable table
ALTER TABLE mytable NOCHECK CONSTRAINT myconstraint


-- enable the myconstraint constraint in the table
ALTER TABLE mytable CHECK CONSTRAINT myconstraint


SCJP1.4
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
You may be able to drill further into metadata and actually get all the constraints so you can follow the dependencies and insert the tables in the required order. I did a school project that build a model of the database by parsing DDL just so I could handle these dependencies.

Disabling and enabling the constraints is far simpler but runs a risk of failing when you put them back on due to invalid values.


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
M Burke
Ranch Hand

Joined: Jun 25, 2004
Posts: 383
Thanks for the help guys. Turning off the constrants seems to be the easy way out. Since the data is valid in one db, it should...hopefully...be valid in an identical schema elsewhere.
 
 
subject: Importing data into a database with a complex schema using Java
 
Similar Threads
Microsoft SQL Server version
Stimulate (simulate?) Discussion
Testing of data class instance(s)
hibernate disable foreign keys
Disabling constraints in DB2