SQL DDL — CREATE, ALTER, DROP, TRUNCATE: Worked examples

The scenario matrix

Every DDL problem is really one of these case classes. Think of the matrix like the four quadrants of a graph: each cell is a distinct situation with its own rule, and if you skip a cell you'll hit it unprepared in real life. The "Example / Case" column below is the anchor: Example n on this page = Case letter, so "Example 6" and "Case F" are the same worked example — the letter names the concept, the number names its position.

#	Case class	What makes it tricky	Worked in
A	CREATE from scratch, empty world	nothing to conflict with — but order matters (parent before child)	Example 1 = Case A
B	ALTER on an empty table	no rows to violate rules → almost anything allowed	Example 2 = Case B
C	ALTER on a populated table	existing rows can violate a new constraint	Example 3 = Case C
D	DROP with no dependents	clean removal; irreversibility is engine-specific	Example 4 = Case D
E	DROP with a foreign-key dependent (referential block)	DB refuses; `CASCADE` needed	Example 5 = Case E
F	TRUNCATE + attempted ROLLBACK (auto-commit trap)	rows do not come back [MySQL/InnoDB]	Example 6 = Case F
G	TRUNCATE blocked by a foreign key	won't run at all	Example 7 = Case G
H	Degenerate / zero-effect input (`IF EXISTS` on an absent table)	must produce no error, no change	Example 8 = Case H
I	Real-world word problem (renovate a live schema safely)	combine ALTER + DEFAULT + backup thinking	Example 9 = Case I
J	Exam twist (spot the difference: DELETE vs TRUNCATE vs DROP)	choosing the right verb under constraints	Example 10 = Case J

We'll walk the whole ecosystem below. Keep this figure in mind — it shows the two tables and the arrow (the foreign key) that ties them together, because most "tricky" cases are about that arrow.

Intuition Figure 1 walkthrough — read every piece

Alt text: two rounded boxes — a violet departments box (top-right) and a magenta students box (bottom-left) — joined by an orange arrow labelled FOREIGN KEY.

Violet box (top-right) = departments — the parent. Its id column is the PRIMARY KEY, drawn first because a child can only point at something that already exists.
Magenta box (bottom-left) = students — the child. Its dept_id column carries the note --> departments.id.
Orange arrow = the FOREIGN KEY. It always points from child to parent. This single arrow is the source of almost every "tricky" case: it blocks DROP (Case E), blocks TRUNCATE (Case G), and dictates CREATE order (Case A).
Bottom caption in the figure restates the rule: dept_id must match an existing departments.id.

Case A (Example 1) — CREATE in an empty world (order matters)

Worked example Build a

departments table, then a students table that points to it. You have an empty database. Write DDL so that every student must belong to a real department.

Forecast: Which table do you CREATE first — and what breaks if you reverse the order?

Steps:

Create the parent first:
```
CREATE TABLE departments (
    id   INT PRIMARY KEY,
    name VARCHAR(40) NOT NULL
);
```
Why this step? A FOREIGN KEY can only reference a column that already exists and is UNIQUE/PRIMARY KEY. If departments doesn't exist yet, the reference has nothing to point at.

Create the child that references it:

CREATE TABLE students (
    id      INT PRIMARY KEY,
    name    VARCHAR(50) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

Why this step? Now the arrow in Figure 1 has a valid target. The DB records the dependency students.dept_id → departments.id.

Verify: Reverse the order (create students first) and the second CREATE errors. Console output (identical idea on both engines):

[PostgreSQL] ERROR:  relation "departments" does not exist
[MySQL]      ERROR 1824 (HY000): Failed to open the referenced table 'departments'

So the rule is: parent tables before child tables. Row count in both after success: 0 rows (structure only, no data yet).

Case B (Example 2) — ALTER an empty table (freedom)

Worked example Add a

NOT NULL column with no default to an empty table. students was just created and has 0 rows. Run:

ALTER TABLE students ADD email VARCHAR(100) NOT NULL;

Forecast: Does this fail? (Recall the parent note warned about NOT NULL without DEFAULT.)

Steps:

The DB scans existing rows to check whether any would violate NOT NULL. Why this step? A new NOT NULL column means "every row must have a value here." The engine must confirm no current row breaks that.
There are zero rows, so the "for every row" check is vacuously true — nothing to violate. Why this step? A rule over an empty set is always satisfied (there is no counter-example).

Verify: Statement succeeds on both engines. Console:

[PostgreSQL] ALTER TABLE
[MySQL]      Query OK, 0 rows affected

Contrast with Case C, where the same statement on a populated table fails. Row count stays 0 rows. This is the degenerate-empty cell: constraints are cheap when there's no data to offend them.

Case C (Example 3) — ALTER a populated table (rows fight back)

Worked example Same statement, but now the table has data.

students has 3 rows (ids 1, 2, 3), none with an email yet. Run:

ALTER TABLE students ADD email VARCHAR(100) NOT NULL;   -- (attempt 1)

Forecast: Will it succeed like Case B did?

Steps:

Engine checks each of the 3 rows against NOT NULL. Why this step? A newly added column has value NULL for every pre-existing row until told otherwise.
All 3 rows would hold NULL in email → all 3 violate NOT NULL. Why this step? There is no value to put there, so the constraint can't be met. Statement fails. Console:
```
[PostgreSQL] ERROR:  column "email" of relation "students" contains null values
```
The fix — supply a DEFAULT:
```
ALTER TABLE students ADD email VARCHAR(100) NOT NULL DEFAULT 'unknown@x.com';  -- (attempt 2)
```
Why this step? The default back-fills all 3 rows with a legal non-NULL value, so the NOT NULL rule is satisfied the instant it's applied.

Verify: Attempt 2 succeeds; all 3 rows now read email = 'unknown@x.com'. Count of rows still 3; count of NULL emails = 0. This is exactly the difference the parent note flagged — the non-empty twin of Case B.

Case D (Example 4) — DROP with no dependents (clean, irreversibility depends on engine)

Worked example Drop an isolated table.

A throwaway table temp_log (5 rows) is referenced by nothing. Run:

DROP TABLE temp_log;

Forecast: After this, can ROLLBACK bring it back?

Steps:

DB checks dependents. There are none, so removal is allowed. Why this step? No foreign key points at temp_log, so no referential integrity is at risk (unlike Case E).
The schema definition and all 5 rows are deallocated. Why this step? DROP removes the object outright, not row-by-row.
Can you undo it? This is engine-specific:
- [MySQL/InnoDB] DROP auto-commits; a following ROLLBACK has no target — the table is gone permanently.
- [PostgreSQL] if the DROP ran inside an explicit transaction (BEGIN; DROP TABLE temp_log;), a ROLLBACK restores the table because PostgreSQL has transactional DDL. Outside a transaction it commits immediately.

Verify: After the auto-committed case, SELECT * FROM temp_log; errors:

[PostgreSQL] ERROR:  relation "temp_log" does not exist
[MySQL]      ERROR 1146 (42S02): Table 'college.temp_log' doesn't exist

Rows destroyed: 5. Reversibility: none in MySQL/InnoDB; possible in PostgreSQL only inside a transaction. Either way — back up first.

Case E (Example 5) — DROP blocked by a foreign key (the referential wall)

Worked example Try to drop the parent while a child points at it.

Using the schema from Case A, students.dept_id references departments.id. Run:

DROP TABLE departments;

Forecast: Success, silent skip, or error?

Steps:

DB looks for dependents of departments. It finds the foreign key on students. Why this step? Dropping departments would leave students.dept_id values pointing at rows that no longer exist — broken referential integrity.

The DB refuses. Console:

[PostgreSQL] ERROR:  cannot drop table departments because other objects depend on it
[MySQL]      ERROR 3730 (HY000): Cannot drop table 'departments' referenced by a foreign key constraint

Why this step? Protecting integrity is the DB's job; it won't silently orphan child rows.

To force it, opt in to cascade [PostgreSQL]:
```
DROP TABLE departments CASCADE;
```
Why this step? CASCADE tells the DB "also drop the dependent constraint (and, depending on engine, dependent objects)." You are consciously accepting the consequence — see Referential Integrity & CASCADE. In MySQL/InnoDB the CASCADE keyword is accepted on DROP TABLE but is a no-op — MySQL parses it and silently ignores it, so it does not remove the dependency for you. You must instead first drop the FK (ALTER TABLE students DROP FOREIGN KEY fk_dept;) or drop students first.

Verify: Plain DROP → error (0 objects removed). DROP ... CASCADE [PostgreSQL] → departments gone and the FK on students removed; students itself survives with an orphaned dept_id column. This is the dependent twin of Case D.

Case F (Example 6) — TRUNCATE then ROLLBACK (the auto-commit trap) [MySQL/InnoDB]

Worked example The classic exam trick.

students has 100 rows. In MySQL/InnoDB you run:

TRUNCATE TABLE students;
ROLLBACK;

Forecast: How many rows remain after the ROLLBACK?

Steps:

TRUNCATE is DDL, so it fires an implicit COMMIT before executing [MySQL/InnoDB]. Why this step? DDL auto-commits in MySQL (parent note's key property). Any open transaction is finalized first.
The table is reset to empty by deallocating its data pages. Why this step? TRUNCATE doesn't delete rows one-by-one; it drops whole storage pages — see the transaction boundary in Figure 2.
ROLLBACK runs — but the transaction it would undo was already committed in step 1. Why this step? There is nothing pending to reverse; rollback has no effect.

Note the engine: in PostgreSQL, TRUNCATE is transactional inside a BEGIN ... ROLLBACK block, so the rows would come back there. The trap below is specifically the MySQL/InnoDB behaviour, which is what most exams test.

[!intuition] Figure 2 walkthrough — read every piece

Alt text: two horizontal timelines. Top (violet label, "TRUNCATE / DDL") shows three dots — TRUNCATE, then an orange "implicit COMMIT", then a violet "ROLLBACK (no target)" — ending at 0 rows. Bottom (magenta label, "DELETE / DML") shows two dots — DELETE, then an orange "ROLLBACK (restores!)" — ending at 100 rows.

Top timeline = TRUNCATE (DDL, MySQL/InnoDB). Left dot = you fire TRUNCATE. Middle orange dot = the engine's implicit COMMIT — this is the killer, it happens before your rollback. Right dot = your ROLLBACK, which now has no target. Italic caption: rows already gone & committed → 0 rows.
Bottom timeline = DELETE (DML). Left dot = DELETE. There is no implicit commit. Right orange dot = ROLLBACK restores everything. Italic caption: no commit until you say so → 100 rows return.
The contrast the figure teaches: the only structural difference is the middle orange "implicit COMMIT" dot on the DDL line. Remove that dot and both would be undoable.

Verify: After the two statements, the row count is confirmed:

mysql> SELECT COUNT(*) FROM students;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

Rows remaining after TRUNCATE+ROLLBACK [MySQL/InnoDB] = 0 rows. Had you used DELETE FROM students; (DML, fully transactional), the ROLLBACK would restore all 100 rows. The whole trap hinges on TRUNCATE = DDL = auto-commit in MySQL/InnoDB.

Case G (Example 7) — TRUNCATE blocked by a foreign key

Worked example TRUNCATE isn't always faster — sometimes it just refuses.

departments is referenced by students.dept_id. Run:

TRUNCATE TABLE departments;

Forecast: Does it empty the table, or error?

Steps:

DB sees departments is the target of a foreign key from students. Why this step? Emptying departments could leave students rows pointing at deleted departments — an integrity break the DB won't allow and can't fix per-row (TRUNCATE has no WHERE, no cascade-per-row).

The statement errors. Console:

[MySQL]      ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint
[PostgreSQL] ERROR:  cannot truncate a table referenced in a foreign key constraint

Why this step? Unlike DELETE (which can check row-by-row) TRUNCATE is all-or-nothing at the page level, so the engine blocks it outright.

Verify: Rows in departments unchanged. To empty it you'd either use DELETE FROM departments; (which checks integrity per row and errors only on referenced rows) or TRUNCATE departments CASCADE; [PostgreSQL] (which truncates the children too). This is the FK-blocked twin of Case F.

Case H (Example 8) — Degenerate input: DROP something that isn't there

Worked example Idempotent scripting.

A deploy script runs on a fresh DB where students was never created. It contains:

DROP TABLE students;              -- version A
DROP TABLE IF EXISTS students;    -- version B

Forecast: Which version lets the script keep running, and which one halts it?

Steps:

Version A: DB looks for students, doesn't find it → raises an error. Script aborts. Console:
```
[PostgreSQL] ERROR:  table "students" does not exist
[MySQL]      ERROR 1051 (42S02): Unknown table 'college.students'
```
Why this step? Plain DROP treats "absent" as a failure.
Version B: the IF EXISTS guard makes "absent" a no-op — no error, no change (at most a warning). Console:
```
[PostgreSQL] NOTICE:  table "students" does not exist, skipping
[MySQL]      Query OK, 0 rows affected, 1 warning
```
Why this step? For an idempotent script (safe to re-run), a missing object should be a shrug, not a crash.

Verify: Version A → error, 0 objects touched, script dies. Version B → 0 objects touched, script continues. This is the zero/degenerate cell: correct behaviour on empty input is "do nothing, quietly."

Case I (Example 9) — Real-world word problem: renovate a live table safely

Worked example The college goes international.

The live students table has 2,000 rows. Management wants a country column, required, defaulting to India, and the old name column renamed to full_name. Do it without losing data or crashing on the existing rows.

Forecast: In what order do you run the statements, and where is the landmine?

Steps:

Back up first (conceptually): in MySQL/InnoDB DDL auto-commits, so a mistake here is permanent; in PostgreSQL you could wrap it in a transaction, but a backup is still best practice. Why this step? No ROLLBACK will save you on MySQL (Cases D, F).
Add the required column with a default:
```
ALTER TABLE students ADD country VARCHAR(30) NOT NULL DEFAULT 'IN';
```
Why this step? This is Case C: 2,000 populated rows would each violate NOT NULL without the default. The default back-fills all 2,000 with 'IN'.
Rename the column:
```
ALTER TABLE students RENAME COLUMN name TO full_name;
```
Why this step? Renaming touches only the definition, not the row values — the 2,000 names ride along untouched. This is why we don't need DML here.

Verify: After step 2, count of rows with country = 'IN' = 2000; count of NULL countries = 0. After step 3, SELECT full_name FROM students; works and SELECT name FROM students; errors. No rows lost. Landmine avoided = the missing default in step 2.

Case J (Example 10) — Exam twist: pick the right verb under constraints

Worked example Read the requirement, choose ONE of DELETE / TRUNCATE / DROP.

"Remove only students older than 25, keep everyone else and the table." Which verb?

Forecast: Guess before reading — the word only is the whole clue.

Steps:

"Only some rows" ⇒ you need a filter (WHERE). Why this step? TRUNCATE has no WHERE — it's all-rows-or-nothing. DROP removes the whole table. Both fail the requirement.
Rule out each wrong verb explicitly:
- TRUNCATE TABLE students; → empties all rows, ignoring the age filter. Wrong.
- DROP TABLE students; → deletes the table itself, structure and all. Wrong. Why this step? Naming why each competitor fails is how exams test that you understand the verbs, not just memorised one.
The correct choice is DELETE (DML, not DDL), with the correct table name students and a terminating semicolon:
```
DELETE FROM students WHERE age > 25;
```
Why this step? Only DELETE filters (WHERE age > 25), keeps the table, and is rollback-able inside a transaction on both engines (it's DML, so no auto-commit).

Conclusion: the answer is DELETE. TRUNCATE and DROP are eliminated by the single word "only", which demands a WHERE filter that neither verb supports.

Verify: If the table had 100 students and 12 were older than 25, then after DELETE FROM students WHERE age > 25;:

[MySQL] Query OK, 12 rows affected

SELECT COUNT(*) FROM students; = 88, and SELECT COUNT(*) FROM students WHERE age > 25; = 0. TRUNCATE would have wrongly left 0 rows; DROP would have left no table.

Recall Rebuild the whole matrix from memory

Which cell (Example/Case) needs a DEFAULT? ::: Example 3 = Case C — ALTER adding NOT NULL to a populated table. Which two cells involve a foreign key blocking you? ::: Example 5 = Case E (DROP) and Example 7 = Case G (TRUNCATE). Which cell proves TRUNCATE can't be rolled back — and on which engine? ::: Example 6 = Case F, specifically MySQL/InnoDB (PostgreSQL can roll it back inside a transaction). Which verb is the answer when you must filter rows? ::: DELETE (Example 10 = Case J) — it's DML, not DDL. What keyword makes a DROP script idempotent? ::: IF EXISTS (Example 8 = Case H).

Recall One-line summary of the whole page

The trickiness of any DDL statement comes from three forces: existing rows (Cases B/C), the foreign-key arrow (Cases A/E/G), and auto-commit — which is real on MySQL/InnoDB but relaxed on PostgreSQL (Cases D/F). Master those three and every cell is just a combination.

See also: Constraints — PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, Normalization, Indexes, Referential Integrity & CASCADE.