SQL Constraints & Schema DDL

The Statement Categories — A Classic Interview Question

Category	Stands for	Commands	Purpose
DDL	Data Definition Language	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`	Defines/changes schema
DML	Data Manipulation Language	`INSERT`, `UPDATE`, `DELETE`	Modifies row data
DCL	Data Control Language	`GRANT`, `REVOKE`	Manages access/privileges — see [[SQL Security & Injection Defense]]
TCL	Transaction Control Language	`COMMIT`, `ROLLBACK`, `SAVEPOINT`	Controls transactions — see [[SQL Transactions & ACID]]

DDL statements auto-commit implicitly in most databases — you cannot ROLLBACK a DROP TABLE once it's executed, which is a genuinely important operational fact, not trivia.

DELETE vs TRUNCATE vs DROP — Know Cold

Aspect	DELETE	TRUNCATE	DROP
Type	DML	DDL	DDL
Removes	Rows (optionally filtered)	All rows	The table itself
WHERE clause	Yes	No	No
Rollback	Yes, within a transaction	No — auto-commits	No
Triggers fire	Yes	Usually no	N/A
Speed on bulk removal	Slow (per-row logging)	Fast (page-level)	Fast
Structure remains	Yes	Yes	No

DELETE FROM orders WHERE status = 'cancelled';   -- selective, rollbackable
TRUNCATE TABLE staging_data;                     -- fast full wipe, keeps schema
DROP TABLE old_archive;                          -- removes the table entirely

The Six Constraints

Constraint	Enforces
`NOT NULL`	Column can't store NULL
`UNIQUE`	All values distinct (NULLs usually still allowed, even multiple)
`PRIMARY KEY`	`UNIQUE` + `NOT NULL`, exactly one per table, usually the clustered index
`FOREIGN KEY`	Value must exist in the referenced table's PK/UNIQUE column
`CHECK`	An arbitrary boolean condition must hold (`age >= 18`)
`DEFAULT`	Value used when `INSERT` omits the column

CREATE TABLE persons (
    id          INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    age         INT CHECK (age >= 18),
    country     CHAR(2) DEFAULT 'IN',
    manager_id  INT,
    FOREIGN KEY (manager_id) REFERENCES persons(id) ON DELETE SET NULL
);

Foreign Key `ON DELETE` / `ON UPDATE` Actions

Action	Behavior
`CASCADE`	Propagate the change to child rows
`SET NULL`	Child FK becomes NULL (column must be nullable)
`RESTRICT` / `NO ACTION`	Block the parent change if children still reference it (safest default)

Best practice: default to RESTRICT — fail loud and force explicit cleanup. CASCADE DELETE is rarely safe outside tightly-owned parent/child relationships (e.g., order_items genuinely can't exist without their order). Always index FK columns explicitly — MySQL InnoDB and PostgreSQL do not auto-create an index on a FK column, so JOINs against it and parent-row updates/deletes can be far slower than expected without one (see [[SQL Indexes & Query Performance]]).

Constraints vs. Indexes — Easy to Conflate

A constraint is a rule about valid data; an index is a data structure for fast lookup.
PRIMARY KEY and UNIQUE do implicitly create an index. FOREIGN KEY does not auto-create one. CHECK and NOT NULL create no index at all.

AUTO_INCREMENT / SERIAL / IDENTITY — Gotchas

CREATE TABLE users (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);  -- modern PostgreSQL

Generated numbers can have gaps — a rolled-back INSERT still consumes a sequence value.
TRUNCATE resets the counter; DELETE does not.
Don't expose auto-incrementing IDs in public URLs (sequential enumeration is a real security concern — the same reasoning as [[Design a URL Shortener]]'s discussion of why sequential short codes leak business volume); use a UUID for anything client-facing.

Interview Drills

Is TRUNCATE a DDL or DML statement, and why does it matter? — DDL — it auto-commits and can't be rolled back mid-transaction, unlike DELETE (DML), which respects the surrounding transaction and can be undone with ROLLBACK before commit. This distinction is exactly why TRUNCATE is fast (minimal logging) but risky if issued by mistake.
Why do you need to manually add an index on a foreign key column, when the FK constraint itself already exists? — A FOREIGN KEY constraint enforces referential integrity (rejecting orphan rows) but does not automatically create a lookup index in most databases — without one, JOINs against the FK column and cascading updates/deletes on the parent table can force a full scan of the child table.
A CHECK (price > 0) constraint exists on a price column, but a bulk import inserted negative prices anyway. What's the likely cause? — Either the constraint was added after the bad data already existed (constraints only guard future writes unless explicitly validated against existing rows), or — historically relevant — an old MySQL version (pre-8.0.16) silently parsed but didn't enforce CHECK constraints at all.

Cross-links

[[SQL Transactions & ACID]] · [[SQL Indexes & Query Performance]] · [[SQL Security & Injection Defense]]