I have a problem with my SQL script.
I have 2 tables, a user table, and a user subscription table, with a foreign key from the user table. I created a simple script simply consisting of insert statements to insert some records into these 2 tables for testing purposes. I would insert the records to the user table first, then the corresponding records into the user subscription table. I only ran a commit at the end of the file.
The script ran fine when i ran it in Toad, yet when the DBA ran the script in production there was a "integrity constraint violated - parent key not found" for every user subscription record i tried to insert. Should i be doing a commit after inserting the user records before trying to insert the child records, or something?
Would really appreciate some advice on this, thanks a million!!
If you insert the parents first, then the children, and only commit at the end, that should work just fine. Or if the foreign key constraint is set to DEFERRED than it doesn't matter which you insert first.