DELETEs Are Difficult
notso.boringsql.com
DELETEs Are Difficult
1–10 of 121 posts
Re: DELETEs Are Difficult
#2 DELETE FROM films;
DELETE FROM films YES-I-KNOW-WHAT-I-AM-DOING;Re: DELETEs Are Difficult
#3Most databases I used have a Status column we could mark as active, inactive, or deleted. That way, you can see what records were marked as deleted and change them back in case of accidental deletion.
Keep record retention with the Date_Modified column so you can use SQL delete to remove those deleted records that are older than a year or so.
Re: DELETEs Are Difficult
#4DELETE FROM films; I'm surprised databases makes it so easy to just delete an entire table. I think the command should be DELETE FROM films YES-I-KNOW-WHAT-I-AM-DOING;
Agreed, I've long been thinking that DELETE should require a WHERE clause.
If you really want to just delete everything, just do WHERE 1=1.
Re: DELETEs Are Difficult
#5Most databases I used have a Status column we could mark as active, inactive, or deleted. That way, you can see what records were marked as deleted and change them back in case of accidental deletion. Keep record retention with the Date_Modified column so you can use SQL delete to remove those deleted records that are older than a year or so.
I do something similar, but instead keep a "date_deleted" column null by default, and the "active" column as a boolean.
That way, I kill two birds in one stone by having a dedicated column for last deletion (instead of updating a record that is supposedly deleted) and the status just as a boolean instead of some enum, or integer or string.
Re: DELETEs Are Difficult
#6For many of the most painful deletion questions, the root problem is that when the software was first made the stakeholders/product-org didn't think about use-cases for deleting things. At best, they assume a "do not show" property can be placed onto things, which falls apart when you get to legal issues that compel actual removal.
Re: DELETEs Are Difficult
#7For many of the most painful deletion questions, the root problem is that when the software was first made the stakeholders/product-org didn't think about use-cases for deleting things. At best, they assume a "do not show" property can be placed onto things, which falls apart when you get to legal issues that compel actual removal.
>software was first made the stakeholders/product-org
Practically all building, physical and software is made for a purpose first, the process is mainly an obstacle that needs to be minimized. A piece of software is trying to solve a problem just like a door is. It's driven by economics where the recipients don't want to pay any more than they need to and someone is always willing to undercut you by cutting corners.
Re: DELETEs Are Difficult
#8DELETE FROM films; I'm surprised databases makes it so easy to just delete an entire table. I think the command should be DELETE FROM films YES-I-KNOW-WHAT-I-AM-DOING;
Agreed, I've long been thinking that DELETE should require a WHERE clause. If you really want to just delete everything, just do WHERE 1=1.
UPDATEs should require a WHERE clause too.
At which point we could just say all SQL should have a WHERE clause.
Re: DELETEs Are Difficult
#9A big asterisk that should be added to the article is that all that applies to Postgres.
Other databases have their own deletion mechanisms (and deletion quirks)
It's a very good article otherwise.
Re: DELETEs Are Difficult
#10DELETE FROM films; I'm surprised databases makes it so easy to just delete an entire table. I think the command should be DELETE FROM films YES-I-KNOW-WHAT-I-AM-DOING;
MySQL has this as default as far as I recall. But then I never delete, I just set "deleted" to yes.