SQL DDL — CREATE, ALTER, DROP, TRUNCATE: Question bank

The state pictures below anchor these ideas before you drill.

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

True or false — justify

TRUNCATE is a kind of DELETE, just faster.

False. TRUNCATE is DDL (resets the structure's contents wholesale); DELETE is DML (row-by-row). They live in different families, which is why DELETE is always transactional and can take a WHERE, while TRUNCATE typically cannot.

DROP TABLE and TRUNCATE TABLE both remove the table's rows, so they end in the same state.

False. Both empty the rows, but DROP also deletes the structure (columns, constraints, the table name itself); after TRUNCATE the empty table still exists and you can INSERT into it.

Most DDL statements can be rolled back with ROLLBACK in every database.

False in general. In MySQL/Oracle DDL auto-commits so there is nothing to undo; but in PostgreSQL/SQL Server most DDL is transactional and can be rolled back. The answer genuinely depends on the dialect.

ALTER TABLE ... DROP COLUMN deletes the table.

False. It removes one column; the table and all its other columns and rows survive. Only DROP TABLE removes the whole object.

A PRIMARY KEY column can hold one NULL value as long as it's unique.

False. PRIMARY KEY = UNIQUE plus NOT NULL, so NULL is never allowed in it, unique or not. (See Constraints — PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE.)

VARCHAR(50) always reserves 50 bytes of storage per row.

False. VARCHAR stores only the actual length used (plus a tiny length prefix); 50 is only the maximum. CHAR(50) is the one that pads to a fixed width.

You can always TRUNCATE any table you're allowed to DELETE from.

False. TRUNCATE is often blocked when a foreign key references the table (Oracle prohibits it outright; MySQL/InnoDB rejects it when the FK is enforced), because it bypasses per-row referential checks; DELETE would still work and check each row.

CREATE TABLE with a CHECK constraint validates data only when you later run the app's validation.

False. The CHECK runs inside the database engine on every INSERT/UPDATE (see the client-server figure above), so bad data is rejected even from a buggy or malicious client that skips app-level checks.

Spot the error

ALTER TABLE students ADD country VARCHAR(30) NOT NULL; on a table that already has rows.

Error: existing rows would have no value for country, instantly violating NOT NULL. Fix by adding DEFAULT 'IN' so old rows get a legal value.

TRUNCATE TABLE students WHERE age < 18;

Error: TRUNCATE cannot take a WHERE clause — it empties the whole table by design. Use DELETE FROM students WHERE age < 18; to filter.

CREATE TABLE orders (id INT PRIMARY KEY, id INT);

Error: two columns named id. Column names must be unique within a table; rename the second (e.g. order_no).

A migration script whose only setup line is CREATE TABLE students (...); (no drop first). Run it once, it succeeds; run the exact same script a second time, it fails.

The second run errors with "table already exists" because the first run left the table behind. Make it idempotent by prefixing DROP TABLE IF EXISTS students; — IF EXISTS avoids an error on the first run when the table is absent, and the drop clears it before recreating on later runs.

DROP TABLE departments; while students.dept_id references it.

Fails with a dependency error: a foreign key still points at departments. You must drop the FK first or use DROP TABLE departments CASCADE; (see Referential Integrity & CASCADE).

Running TRUNCATE TABLE logs; then ROLLBACK; in MySQL/InnoDB, expecting the rows back.

The rows do not return: in InnoDB TRUNCATE auto-committed before ROLLBACK ran, so there is nothing to undo. (In PostgreSQL the same sequence inside a transaction would bring them back, because there TRUNCATE is transactional.)

Why questions

Why does DDL exist as a separate category from DML at all?

Because you must define the container (typed columns + rules) before any row can live in it — DDL shapes the structure, DML fills it. Furniture needs a room first.

Why is DROP considered irreversible in MySQL/Oracle while DELETE is not?

DROP removes the schema definition itself and auto-commits, so there's no structure left to roll back into; DELETE only touches rows within a still-existing, transactional structure. (In PostgreSQL a DROP inside a transaction can still be rolled back.)

Why does TRUNCATE run so much faster than DELETE on millions of rows?

TRUNCATE deallocates the data pages wholesale and resets the table, instead of removing, logging, and firing triggers for each row the way DELETE does.

Why supply a DEFAULT when adding a NOT NULL column to a populated table?

The DEFAULT fills the new column for existing rows, so they immediately satisfy the NOT NULL rule instead of violating it and aborting the ALTER.

Why does the database refuse to DROP a table that a foreign key references?

To protect referential integrity — dependent rows would point at a table that no longer exists, leaving dangling references. (See Referential Integrity & CASCADE.)

Why is a CHECK constraint safer than validating "the same rule" in the application code?

The app check is skippable — a direct SQL client, another service, or a bug can write straight to the table and bypass it. The CHECK lives at the last gate, the engine itself, so no path around the app can slip bad data in (see the client→server figure).

Why prefer CHAR over VARCHAR for a two-letter country code?

Because the length is genuinely fixed at 2; CHAR(2) avoids the variable-length overhead and signals the fixed nature, whereas VARCHAR is for values whose length varies.

Why does ALTER let you renovate instead of just dropping and recreating?

DROP+recreate would destroy all existing rows; ALTER edits the live blueprint while keeping the data intact — you renovate the room without evicting the furniture.

Edge cases

You TRUNCATE a table with an auto-increment id, then insert one row. What id does it get?

Typically 1 — TRUNCATE usually resets the identity counter, unlike DELETE which leaves the counter where it was.

You add a UNIQUE constraint via ALTER to a column that already contains duplicate values.

The ALTER fails: the constraint can't be satisfied by existing data. You must remove or fix the duplicates first.

DROP DATABASE college; while you are currently connected to college.

It typically fails or is refused because the database is in use; you must disconnect / switch to another database before dropping it.

DELETE FROM students; (no WHERE) vs TRUNCATE TABLE students; — same visible result, but name one behind-the-scenes difference.

Both leave an empty table, but DELETE is logged per row and rollback-able (fires triggers, keeps identity counter), while TRUNCATE is a fast page-reset that (in MySQL/Oracle) auto-commits and usually resets identity.

You ALTER a column's type from INT to SMALLINT on a table holding the value 100000.

It fails or truncates because 100000 exceeds SMALLINT's range; the DB refuses data-losing conversions (or errors) to protect existing values.

TRUNCATE on an indexed table — what happens to the indexes?

The indexes are kept as structures but emptied along with the table, since TRUNCATE rebuilds/resets storage rather than dropping definitions; you don't have to recreate them afterwards (unlike DROP, which removes them entirely).

TRUNCATE on a partitioned table, or on one table in an Oracle FK relationship.

On a partitioned table you can often TRUNCATE ... PARTITION p1 to empty just one partition fast; but if any enabled foreign key references the table, Oracle prohibits TRUNCATE entirely (you must disable the FK or DELETE instead) — stricter than MySQL, which only blocks it while the FK is enforced.

An empty table (zero rows): does TRUNCATE do anything meaningful?

Structurally it's a near no-op for data, but it may still reset the identity counter and (in MySQL/Oracle) auto-commit — so on an empty table the visible effect is mostly the counter reset.

Recall One-line self-test

Which single verb here removes the structure itself, and which verb is fully transactional and rollback-able in every dialect? Answer: DROP is the one that removes structure. DELETE is the fully transactional, always-rollback-able verb — and note it is DML, not DDL. TRUNCATE straddles the line: it empties like DELETE but behaves like DDL (auto-commits in MySQL/Oracle, transactional only in PostgreSQL/SQL Server).