Truncate tables where Referential Integrity exists
-
Comments posted to this topic are about the item Truncate tables where Referential Integrity exists
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
This seems like a very dangerous thing to do from a data governance standpoint. Won't you leave abandoned keys in related tables this way?
Ted Seeber
I find your secrets in your data.
http://www.informaitonr.us -
the code creates the drop commands and the create commands, it's for dev use so test it first to ensure it does what you require
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
The idea is good. However, order of executing generated scripts is important. When you DROP constraint, the its definition is lost, there will be no way to generate ADD CONSTRAINT.
For safety sake, I would use this order of executing blocks:
- run scripts to generate set of ALTER TABLE .. ADD CONSTARINT.. FOREIGN KEY. Store generated scripts somewhere, [Place 1]. When you later actually DROP constraints, you will still have scripts to re-generate them.
- then run scripts to generate set of DROP constraints. Store generated DROP CONSTRAINTS statements [Place 2]
- run DROP CONSTRAINTS statements . If you do not have statements to re-create constraints, you will be in trouble. DROP CONSTRAINT removes constraint definition from sys tables, so there is nothing to use to re-create ADD CONSTRAINT statements.
- generate TRUNCATE statements
- run TRUNCATE statements to remove data
- run set of ALTER TABLE.. ADD CONSTRAINT statements, to recreate FK constraints. You have stored them somewhere before beginning any destructive steps (DROP CONSTRAINT, TRUNCATE or DELETE table)
Where I work, we have been using this approach for a while, to clean up tables in DEV or QA environment.
Zidar's Theorem: The best code is no code at all...
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply