Deleting Data from a Database, Just don't do it
I recently heard about a company that because they deleted what they thought was lookup data in a table ran into all kinds of relational issues with data in other tables that referenced that lookup table. On top of that it was a production database.
With very few exceptions, don't delete data from databases. SQL makes it very easy, maybe too easy to delete data from tables;
DELETE FROM myTable;
GO
GO
There are some applications that collect tons of transactional data that fill up hard drives pretty quickly. That being said, hard drive space is cheap now, and this data can be offloaded to another system if it needs to be moved to clear up disk space. This is a fairly common task used in database warehousing.
Maybe another view of this lesson is to design the database correctly and not have all the data integrity logic in your application layer.
Since I am a MySQL admin I frequently access the database directly, which means that there are virtually no constraints to what I can do.
When I wish to remove data from MySQL, I will either create a few new field for the table(s) in question called field_status and status_mod_date and then change the status accordingly, or simply move the "deleted" data to a clone of the table.
All this to say, an important part of architecting a system is create methods (or at least strategies) for maintaining that system. This includes backups, purges, garbage collection, etc. If there is a interface in your application for all these purposes, there will be no reason to risk going in behind the scenes and mucking things up.
btw, another more important reason data shouldn't be deleted....state and federal laws regarding retention of data, specifically PII. everyone really should look into this more often. there are cases where data can and should be deleted and others where a 'soft' delete should be used.
then again, i'm a big fan of TRUNCATE TABLE FOO :)
It’s really good written and I fully agree with You
on main issue, btw. I must say that I really enjoyed
reading all of Your posts. It’s interesting to read ideas,
and observations from someone else’s point of view… it makes
you think more. So please try to keep up the great work all the time.
Greetings