How would you solve following scenario:
1. Into database were inserted data into a table. Let's say the data has primary key "key1" and no database generated/automated sequence value/ID existsfor that datarecord, so basically was inserted following data:
Insert into T(key,...) values ('key1',...);
2. Ca 1 months the data have been used and now child tables reference the data. The child tables have also child/related tables that reference that data. So there is ca 60 tables now related to data inserted in step/point 1. So basically now we related data as:
Insert into F1(F1_ID, key,...) values (100, 'key1',...);
Insert into F2(F2_ID, key,...) values (101, 'key1',...);
Insert into F1F2(F1F2_ID, F2_ID,...) values (200, 100,...);
...
3. After 1 month when step/point 1 was completed new task were given: rename 'key1' to 'newkey1', because the old name/key was misleading/wrong.
4. Now development and testing databases have wrong data and must be corrected. The development model goes "incremental" way, so that script for step 1 produced file "step1.sql" and into production the final install-package will run all scripts one-by-one as following way:
run script1.sql
run script2.sql
...
run scriptN.sql
And into development/test databases the scripts/scriptfiles are runned one at a time, lets say on 14-jan script1.sql is runned/installed into testDatabase1, then on 16-jan the script2.sql is installed into testDatabase1, and on 17-jan perhaps scripts script1.sql and script2.sql are installed into testDatabase2 and so on, testing personell test the in different environments/databases which have different versions of scripts. So now in testDatabase1 and maybe somewhere more is wrong data from step1.
The current wrong data is not important at the moment, because it is not in Production database yet, so the data can be deleted. Into Production database big file called "install_all_scripts_incrementally.sql" is runned, it will contain script1.sql from step1 and also immediately it will contain/run next scripts, it will install script2.sql that corrects the wrong data, corrects /rollbacks the script1.sql, and mainly this script2.sql is needed for testing databases because it corrects data there and Production database will not have wrong data at all because script2.sql is runned immediately after script1.sql.
5. How you would suggest to create script2.sql that corrects now script1.sql by renaming 'key1' to 'newkey1'? There is ca 60 tables which will be affected/crrected.
Maybe fastest/convienient way would be to write recursive query that outputs all related tables that are related somehow to table T, and then order the 60 table names ascendingly in the meaning of child-tree deepest leaves, and then insert into script2.sql the delete-from clauseses of those tables for key "key1"?
Maybe the best way is still to update the child/related and main table data?
Or perhaps there exists possibility to run "cascading"statement?
Or should the best way be to also disable constraints in database firstly- i think that option is dangerous.
So simply put: i inserted into database main table wrong data and the related tables now reference the data, and now i need to rename/correct the wrong data in main table, and it is not important will the child tables referenced data be deleted or not, how would you do?
Edited by: CharlesRoos on 14.01.2011 14:46
Edited by: CharlesRoos on 14.01.2011 14:47
Edited by: CharlesRoos on 14.01.2011 14:49