Rule of thumb for me is that every DELETE or UPDATE query should start life as a SELECT query.

The eight stages of DELETE:

- SELECT what you're deleting;

- fix the mistake in the query (there's always one);

- SELECT again;

- BEGIN TRANSACTION;

- DELETE;

- SELECT to confirm the right deletion -- twice for good measure, and maybe ROLLBACK once or twice if you're feeling twitchy;

- Hover finger anxiously over the un-entered COMMIT command for several seconds while resisting Dunning–Kruger effect;

- COMMIT. :)

Here's my insanity

Give the table an DeletedTimestamp and DeleatedReason column.

Mark the records as deleted.

Run around like a lunatic and fix queries so they don't show "deleted" columns to the rest of the app.

Make a page in the application where people can "restore" deleted items. Because they do stupid things too once in a while.

My fix for this is to rename the table to something like _b_<table_name> then create a view <table_name> that excludes the 'deleted' values (based on the value in DeletedReason column). That way you make one change to the structure when you create the column, and everything else Just Works

We use the same approach and specifically use a "...withdeleted" suffix for the table - you got a table of containing widget objects there? how about a "widgetwithdeleted" table.

until you hit requirements that data must really be gone from the system

at least you need something like DELETE FROM foo WHERE deleted < NOW() - '30 days'::timedelta (or whatever the correct syntax is)

Yep, I do the same. Depending on size of data being affected, level of table RI in place,and necessity of seeing the data again later, or client calling back the following day/week having changed their mind and can we 'undo' that, I will often tack in a select into tablename_changes_jan7_2020 or some such convention to grab the unchanged records. Qty, backup retention options etc also play into this so use some common sense of course. If you worry about space, just set an drop table job up to clear it after a month or whatever. Lots of these CYA tricks if you want to make sure you don't shoot yourself in the foot.

I do the same thing.

However, it won't save you if realize that there's an issue after you commit. Maybe I'm just paranoid but I like to have both a snapshot before a major change, plus I'm fond of having a spare (via delayed replication or log shipping).

Ah, I forgot to include the twelve stages of proactive, redundant backups. :) I usually do the same thing -- local snapshots if the database is small and the app is less critical... or a careful review of the central db backup history and a documented rollback plan if it's a bigger system.

Large DELETEs muck up table statistics and cached joinplans, which is why a lot of enterprise databases have an IsDeleted column on their large tables. Setting things up this way has the added benefit of being able to easily "delete" things without too much anxiety.

Ideally, all SELECT queries are forced through a view layer (which already has an IsDeleted = 0 restriction), and you can just schedule a nightly job which deletes or archives all the rows with IsDeleted = 1, then refresh the table statistics automatically.

It's a good idea to never run hand-crafted data mutating queries against a production DB. Nervousness is fine but CTEs are extremely useful - at our company we insist on automated tests around SQL that will be run against the prod DB and that makes me quite happy. Just make sure the devs prove their CTE is correct before letting it touch the data.