SQL DDL — CREATE, ALTER, DROP, TRUNCATE: Visual walkthrough

Before symbols, let us earn our two words.

Step 1 — What even is a table? (the shell and the contents)

WHAT. A table is two separate things stacked together. The first is the shell: the table's name, its columns, each column's type, and the rules (constraints). The second is the contents: the actual rows of data sitting inside that shell.

WHY split them? Because — and this is the whole secret of the page — the four verbs differ only in which of these two they keep and which they destroy. If we don't separate them first, the verbs look like a random list to memorise. Once separated, they become a 2-column checklist.

PICTURE. Look at the figure. The blue frame with the column headers is the shell. The gray rows inside are the contents. They are drawn as two visibly different layers on purpose — burn that picture in.

Figure — SQL DDL — CREATE, ALTER, DROP, TRUNCATE

Step 2 — CREATE: making a shell where there was nothing

WHAT. CREATE TABLE takes empty space and writes a shell into it. No rows yet — an empty box with labelled sections.

$the verb CREATE TABLE name of the shell students (one column + one rule id INT PRIMARY KEY, \dots)$

Reading it term by term:

CREATE TABLE — the instruction "build a new shell."
students — the name we can refer to it by later.
id INT — a column called id whose type is integer.
PRIMARY KEY — a rule glued to that column: unique + not null (see Constraints).

WHY first? You cannot put a row into a table that does not exist. Structure precedes data — always. This is why CREATE is the beginning of every database's life.

PICTURE. The left panel is blank space. The arrow labelled CREATE produces the right panel: a shell (blue frame + headers) with zero rows. Notice: contents = empty, shell = present.

Step 3 — INSERT arrives: contents appear (so we have something to lose later)

WHAT. This step is not DDL — it is DML. But we need rows on the table so the later verbs have something to keep or destroy. INSERT adds contents into the existing shell.

WHY show it? Because the drama of DROP vs TRUNCATE vs DELETE is entirely about what happens to these rows. With an empty table you literally cannot see the difference. So we fill it first.

PICTURE. Same blue shell as Step 2, but now three gray rows sit inside. The shell is untouched; only the contents-layer changed.

Recall Which layer did INSERT change?

Contents only ::: The shell (columns, types, rules) was already there from CREATE; INSERT wrote rows into it.

Step 4 — ALTER: editing the shell while the contents stay put

WHAT. ALTER TABLE changes the shell — add a column, drop a column, rename, change a type, add a rule — without throwing away the rows.

$ALTER TABLE students sub-action ADD new shell piece country VARCHAR(30) rule + fill value NOT NULL DEFAULT ’IN’$

Term by term:

ADD — the kind of edit (could be DROP COLUMN, RENAME, ALTER COLUMN TYPE).
country VARCHAR(30) — the new column we are welding onto the shell.
NOT NULL — the rule "this may never be empty."
DEFAULT 'IN' — the value handed to every existing row, because those rows had no country and a NOT NULL column with blanks would instantly break the rule.

WHY the DEFAULT matters (edge case built in). If you write NOT NULL with no DEFAULT on a populated table, the old rows would each hold NULL in the new column the instant it appears — violating the rule — so the whole ALTER fails and rolls back. The default is the fix.

PICTURE. The blue shell grows a new orange column on the right. The three gray rows survive; each gets IN auto-filled in the new column (shown in orange). Shell changed, contents preserved.

Step 5 — TRUNCATE: wiping the contents, keeping the shell

WHAT. TRUNCATE TABLE empties all rows at once and leaves the shell exactly as-is.

$TRUNCATE TABLE students ⟹ contents \to \emptyset, shell unchanged$

The symbol $\emptyset$ (read "empty set") just means nothing left inside. The shell — columns, types, constraints — is identical to before.

WHY it's a separate command from DELETE. DELETE FROM t removes rows one by one, logging each and checking triggers — slow on millions of rows. TRUNCATE deallocates the whole data region in one stroke, like tipping the whole toy box out instead of removing toys individually. That's why it's fast but why it can't take a WHERE filter and (in most databases) can't be rolled back — there's no per-row log to reverse.

PICTURE. The blue shell + orange column stay perfectly intact; the three gray rows evaporate (shown as faded dashes), leaving an empty-but-fully-defined table.

Recall You run TRUNCATE then ROLLBACK (InnoDB). Rows back?

No ::: TRUNCATE is DDL and auto-commits; the implicit commit fires before your ROLLBACK, so there is nothing left to undo.

Step 6 — DROP: destroying shell and contents together

WHAT. DROP TABLE removes both layers — the rows and the shell. After it, the name students refers to nothing.

$DROP TABLE students ⟹ contents \to \emptyset, shell \to \emptyset$

WHY it is irreversible. There is no longer any structure to roll back into. TRUNCATE could conceivably be undone if the shell survived; DROP annihilates the shell itself, and DDL auto-commits, so it is gone for good. This is why production scripts pair it with IF EXISTS and why you back up first.

PICTURE. Both the blue frame and the gray rows disappear, leaving the same blank space we started from in Step 2. Full circle: the table's life ends where it began.

Step 7 — The degenerate case: DROP on a table nothing points to vs. one under a FOREIGN KEY

WHAT. Two edge cases live here.

Nonexistent table. DROP TABLE students; when students isn't there → error. DROP TABLE IF EXISTS students; → no error, script stays idempotent (safe to re-run).
Referenced table. If another table's FOREIGN KEY points at this one, a plain DROP fails — the database refuses to strand those references.

$DROP TABLE departments FK from students.dept_id blocked$

WHY blocked? Referential integrity: students.dept_id promises "my department really exists." Deleting departments would break that promise for every student. DROP TABLE departments CASCADE; forces it and removes the dependent constraint too — you are explicitly telling the DB "yes, tear down the links as well."

PICTURE. Two mini-scenes. Left: an orange arrow (the FK) runs from students into departments; a red ✗ on the DROP shows it's refused. Right: with CASCADE, the arrow itself is cut (dashed red) and both drops succeed.

Step 8 — Collecting the pattern: the two-column checklist

WHAT. Now we assemble every verb into the grid promised in the intuition — Keep (✓) or Destroy (✗) for each layer.

Verb	Shell	Contents
`CREATE`	build (new)	empty (new)
`ALTER`	✓ edited	✓ kept
`TRUNCATE`	✓ kept	✗ wiped
`DELETE` (DML)	✓ kept	✗ (filterable, rollback-able)
`DROP`	✗ destroyed	✗ destroyed

WHY this is the whole point. Every difference in the parent note's big comparison table is a consequence of this grid. "Does it keep structure?" = the Shell column. "Does it remove data?" = the Contents column. You never memorised anything — you read it.

PICTURE. The verbs plotted as arrows on a 2×2 map: horizontal axis = shell kept → shell destroyed, vertical axis = contents kept → contents destroyed. CREATE lands bottom-left, ALTER bottom-left (contents kept), TRUNCATE top-left, DROP top-right corner.

The one-picture summary

WHAT. One figure compresses the entire life cycle: blank → CREATE → INSERT → ALTER → TRUNCATE → DROP → blank, with each arrow labelled by which layer it touches.

Recall Feynman: retell the whole walkthrough in plain words

Picture a toy box. It has two parts you can point at separately: the box itself with its labelled sections (the shell), and the toys inside (the contents).

CREATE builds a brand-new empty labelled box.
INSERT drops toys in (that's DML, not our four verbs, but now the box isn't empty).
ALTER adds or removes a section of the box, or relabels one — the toys stay put; if a new section demands to never be empty, you hand every existing toy a default value so nothing breaks.
TRUNCATE tips the whole box out in one motion — toys gone, box intact — fast, but you can't pick which toys and you can't scoop them back.
DROP throws the entire box in the bin — toys and box both gone, no undo.
And a safety rule: if another box's label points at this box (a foreign key), the bin refuses your DROP until you either cut the link or say CASCADE.

Every "which is faster / which keeps structure / which is reversible" question is just: does this verb touch the box, the toys, or both?

Recall One-line self-test

Which single verb keeps the shell but empties the contents fastest, with no WHERE and no rollback? ::: TRUNCATE.