We have identical databases in our development, QA and Production environments. As part of our application development we actually generate data. As a result, we port the data from several tables generated in our development environment to our QA enviroment for testing and then to our Production environment for deployment along with the application changes.
Since the involved tables have a foreign key to another table which is *not* migrated, part of the process includes updating that foreign key from environment to environment. The primary key involved is a unique 1 up number that is inserted by trigger and drawn from a sequence.
The perception from my colleagues is that updating the foreign key value is an unnecessary step that is prone to error (I personally disagree - we use TOAD, so we can replace the value when generating the export script-no real issue imho).
Their proposed solution is to remove the sequences and triggers from the QA and Production environment (leaving the Dev environment untouched). When we create a record in the Dev environment the Primary key would be generated. We would then port that value to the QA and then Prod. Then we can move the database records from environment to environment without changing the foreign key.
I have a few problems with this solution including: Having the integrity of one database dependent on values generated in another database and also having a development environment not match the production environment. I'm also concerned about the unintended consequences of this approach (is there greater risk of undermining the integrity of the environments, will addressing the database in QA and PROD become more cumbersome, etc.)
In general it just seems like a wacky solution to a very minor inconvenience but I need to articulate specific problems in order to avoid this happening (also, if it *is* a legitimate approach I'd like to know that too, of course...)
Can any comment on the type of approach, either pro or con, both from experience and from a theoretical perspective?