Data Recovery In Database
Deleting Data from a Relational Database?
I need to perform an assessment on the impact of deleting rows from a relational database. I am not interested in the syntax, but would like to learn more about the risks.
We do have a backup/recovery strategy, but I am curious to know if anybody can give me suggestions on what to include in our impact analysis.
Our data dictionary allows us to see what tables are referenced to and from our target table.
We are reviewing the associations between each table to determine if data integrity is at risk (e.g. if we remove a row from table X, will references to/from table Y be impacted).
Does anyone with experience have any GENERAL suggestions on performing this type of assessment?
The answer is easy. If you’re using a real RDBMS, it already has built in handling of maintaining data integrity on foreign key relationships. Eg. in Oracle, it won’t let you delete referenced data, simple as that.
Eg2 in MySQL, depending on the table engine used, you can choose between Restrict (same behaviour as Oracle), Cascade (deleting records from a table will also automatically delete any records that reference it), or Update (deleting records from a table will update the records that reference that data, setting the foreign key to NULL). This, too, is all inbuilt and doesn’t require any specific application-level relationship validation, or any kind of assessment. They’re standard features.
Of course, this all assumes you’ve set up your foreign key relationships correctly. If you haven’t, you’re doing it wrong. If your RDBMS doesn’t support relationships, you’re doing it wrong. In these cases, performing your assessment is the least of your worries.
Backup and Recovery of Oracle Database Environments with Data Domain