We have a rather big database (about 400'000'000 records) in roughly 100 tables.
The foreign keys in these tables are defined, so that
No Action is taken, when deleting data from a table.
We now have to archive some of these data since only the last year is of any use (from the 9 years of data available).
We decided on a strategy to create a backup database with all the data inside and delete all data except the last year from the production database.
We're asking ourselves what is the best approach to this.
- Should we change the existing foreign keys so that they cascade delete?
- Are there any risks involved with this?
- Should we delete table per table?
- Is there a simple way to change all existing foreign keys so that they cascade delete?
- Is there another even better option?
Thanks in advance