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

==TRUNCATE==. Why: TRUNCATE TABLE t deallocates the data pages so every row vanishes, but the shell (the column list, types, and constraints — the "blueprint") is untouched. DROP would delete the shell too; DELETE also keeps the shell but it is DML, not a DDL verb, and it is slower.

False. The keyword DROP appears, but it is glued to COLUMN phone. It removes only that one column. The table and every other column survive. Only a bare DROP TABLE students; deletes the whole object.

1. CREATE — builds a new object.
2. ALTER — edits an existing object's definition.
3. TRUNCATE — empties all rows, keeps structure.
4. DROP — deletes the whole object (IF EXISTS just stops an error if it is already gone).

CREATE TABLE books (
    id     INT           PRIMARY KEY,          -- unique, not-null handle
    title  VARCHAR(200)  NOT NULL,             -- must be present
    pages  INT           CHECK (pages > 0),    -- domain rule
    isbn   VARCHAR(20)   UNIQUE                 -- no duplicates
);

Why each piece: PRIMARY KEY = UNIQUE + NOT NULL gives every row a guaranteed handle. NOT NULL on title forbids a nameless book. CHECK (pages > 0) rejects zero or negative page counts at the database level, so even a buggy app can't insert them. UNIQUE on isbn stops two books claiming the same ISBN. See Constraints — PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE.

ALTER TABLE books ADD language VARCHAR(10) NOT NULL DEFAULT 'en';

Why the DEFAULT: the moment you add a NOT NULL column, every existing row needs a value. With no default, those rows would instantly violate NOT NULL and the whole ALTER is rejected. The DEFAULT 'en' back-fills every existing row, so the rule is satisfied and the statement succeeds.

DROP TABLE IF EXISTS books;

Why IF EXISTS: on the first run the table is dropped; on the second run it is already gone. A plain DROP TABLE books; would then throw an error and halt the script. IF EXISTS makes the command idempotent — running it again does no harm.

No — the rows are gone permanently. Mechanism: TRUNCATE is a DDL statement, and DDL auto-commits. An implicit COMMIT fires before TRUNCATE runs (and the truncate itself is not undoable). By the time your ROLLBACK executes, the transaction it would undo has already been committed and closed — there is nothing left to roll back. Had you used DELETE FROM books; instead, that is DML and fully transactional, so the ROLLBACK would have restored every row. See Transactions and ACID.

Outcome: the DROP fails with a dependency error. A foreign key still points at departments, and the database refuses to leave the students table pointing at a table that no longer exists — that would break referential integrity. What DROP TABLE departments CASCADE; changes:

1. It forces the drop of departments anyway, and
2. it also drops the dependent foreign-key constraint on students so nothing is left dangling. The students rows survive, but they lose the FK rule. See Referential Integrity & CASCADE.

• Team A → DELETE FROM t WHERE status = 'archived'; — only DELETE accepts a WHERE filter. TRUNCATE has no WHERE; it is all-or-nothing.
• Team B → TRUNCATE TABLE t; — it deallocates data pages wholesale instead of logging 50 million individual row removals, so it is dramatically faster. Rollback isn't needed, which is good because TRUNCATE isn't rollback-able anyway. Why they can't swap: Team A cannot use TRUNCATE because a filter is required. Team B could use DELETE but it would be slow (per-row logging) and log-heavy.

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

Why this order: students names departments(id) in its FOREIGN KEY. The referenced table must already exist when that constraint is created, so departments has to be built first. Reverse the order and the second CREATE fails: it references a table that doesn't yet exist. (Symmetrically, when dropping, you must drop students first, or drop departments with CASCADE.)

ALTER TABLE students ADD email VARCHAR(100) UNIQUE;              -- 1
ALTER TABLE students ADD CONSTRAINT chk_id CHECK (id < 1000000); -- 2
ALTER TABLE students RENAME COLUMN name TO full_name;            -- 3

Why each: step 1 adds a column and attaches a UNIQUE constraint in one go — allowed because no existing email values exist to clash. Step 2 uses a named constraint (chk_id) so it can be dropped by name later. Step 3 RENAME COLUMN edits the live blueprint without touching the stored values. All three are ALTER — the table survives throughout.

Bugs:

1. Drop order is wrong / unsafe. departments is dropped first, but students still has a foreign key on it → dependency error. Drop the child (students) first.
2. No IF EXISTS. On a fresh database (or the second run pattern) a bare DROP TABLE on an absent table errors and halts the script — it is not idempotent.
3. Create order is wrong. students is created before departments, but its FOREIGN KEY references departments(id) → the referenced table doesn't exist yet → error. Create the parent first.

Fixed, idempotent script:

DROP TABLE IF EXISTS students;      -- child first
DROP TABLE IF EXISTS departments;   -- then parent
 
CREATE TABLE departments (          -- parent first
    id   INT          PRIMARY KEY,
    name VARCHAR(40)  NOT NULL
);
 
CREATE TABLE students (             -- then child
    id       INT PRIMARY KEY,
    dept_id  INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

Rule of thumb: drop children → parents; create parents → children. The dependency arrow points from child to parent, so you build in the direction of the arrow and demolish against it.

Approach A — empty the child first, then the parent:

TRUNCATE TABLE students;
TRUNCATE TABLE departments;

With no students rows left, no foreign-key value points at departments, so truncation is now allowed. Consequence: you also lose all students rows — acceptable only if that is intended.

Approach B — use DELETE with cascade behaviour set up on the FK:

DELETE FROM departments;   -- if the FK was defined ON DELETE CASCADE

If the foreign key has ON DELETE CASCADE, deleting a department automatically deletes the students pointing to it. Consequence: it is per-row and logged (slower than TRUNCATE), but it is transactional — you can wrap it in a transaction and roll back if needed. See Referential Integrity & CASCADE and Transactions and ACID.

Build parents before children, demolish children before parents; TRUNCATE/DROP auto-commit so they can't be rolled back; a NOT NULL column added to populated data needs a DEFAULT; and foreign keys make the database refuse any operation that would leave a dangling reference.