Hacker News Reader

Comment by bearjaws | Hacker News Reader

The only thing I would really blame solely on SQL is that UPDATE and DELETE statements don't require you to specify a limit.

I have seen many times in my career where a rogue delete just truncates a table, a simple statement of intent (e.g. LIMIT 1) would tell the query planner that if it is about update/delete more than 1 row, it should error. In fact MySQL actually returns a warning if you do this.

TRUNCATE clearly states your intention to delete everything, but DELETE by default also deletes everything.

There were also definitely some bad paradigms invented as a result of SQL (e.g. all business logic lives in the database as stored procedures or stored functions), but that was bound to happen whenever a shiny new tool comes out and engineers want to use it.

View on HN

> TRUNCATE clearly states your intention to delete everything, but DELETE by default also deletes everything.

I think the other way around.

“DELETE Something” to me means get rid of the whole of Something. “TRUNCATE Something” (in general English, not SQL) to me means to make something shorter, which probably doesn't mean removing it completely.

Perhaps “EMPTY Table” would have been a better choice than “TRUNCATE Table”, but by the time it reached the standard¹ TRUNCATE had long since been picked and until then it was not the responsibility of the SQL standard: you only had DELETE and without a filter it makes sence that this deletes all.

----

[1] note that TRUNCATE was not in the SQL standards until SQL2008, long after it was common in various DMBSs as a minimally logged alternative to DELETE-without-filtering-clauses.

> TRUNCATE clearly states your intention to delete everything, but DELETE by default also deletes everything.

btw. they are completly different.

TRUNCATE basically ignores all transaction semantices for the sake of performance (which can be really really bad) in mysql it can't be rolled back, in postgres it's not mvcc safe. TRUNCATE most often uses either an exclusive lock or some other mechanism.

if you know what you are doing truncate can be helpful, but delete has its benefits as well and truncate should be avoided by beginners.

The other important factor is permissions.

Truncate in MSSQL requires alter permissions, so a user that can wipe a table can also change its definition. Not true with delete.

"There were also definitely some bad paradigms invented as a result of SQL (e.g. all business logic lives in the database as stored procedures or stored functions),"

I hope you don't mean by that that integrity enforcement (checking the compliance of the data with ALL declared business rules) does not belong in the DBMS. Because the exact opposite is true : inside the DBMS is the only place where it belongs. Or if not the only, then certainly still the primary.

Require?

Some UPDATEs don't allow me to specify a limit!

Infuriating stuff.

So don’t COMMIT your UPDATEs.

Reply on news.ycombinator.com