I have a large data 40GB SQL DB that I would like to delete all the data from all the tables, hence my questions: How does one delete data from an SQL Database and Maintaining Referential Integrity+constraints?
Asked
Active
Viewed 74 times
0
-
1Child records first, parent records last. – Dan Bracuk Jun 18 '15 at 11:47
-
possible duplicate of http://stackoverflow.com/questions/1899846/how-to-delete-all-rows-from-all-tables-in-a-sql-server-database – Sagar Shirke Jun 18 '15 at 12:00
-
Which DBMS are yo using? Postgres? Oracle? – Jun 18 '15 at 12:46
1 Answers
0
It depends.
If you want to delete all the data from all the tables you are better off disabling the constraints, truncating the tables, and then re-enabling the constraints.
If you are just deleting some records you need to delete the child records first and then the parents as @dan-bracuk said. Alternatively if you have cascading delete set up you can start at the parent.
If you are deleting many, many records you may still be better off disabling the constraints if you can limit other activity on the tables for the duration.
If you are replicating the tables then you may have some other considerations.
Karl Kieninger
- 8,841
- 2
- 33
- 49