beginner~4h

SQL Fundamentals — DDL, DML & Constraints

SQL (Structured Query Language) is the standard language for managing relational databases. Every backend engineer, data engineer, and solution architect must master SQL fundamentals.

Learning objectives

Explain, in your own words, the difference between DDL, DML, DCL and TCL.
Say what each of the six constraint types actually protects against, not just what it's called.
Explain why constraints belong in the database, not just in application code.

📖 Story

Imagine you're setting up a physical filing cabinet for a new company. Before you file a single document, you do two separate jobs. First, you build the cabinet itself — how many drawers, what labels go on each drawer, whether a drawer can be left empty, whether two folders are allowed to share the same name. That's carpentry and labeling. Only after the cabinet exists do you start the second job — actually putting documents in, taking them out, updating them when they change.

A database asks you to do the exact same two jobs, and it gives each one its own vocabulary. Building the cabinet — creating tables, deciding what columns exist, deciding what rules apply to them — is DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE. Actually working with what's inside — adding, reading, changing, removing rows — is DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE. Two more, smaller vocabularies exist alongside them: DCL (Data Control Language — GRANT, REVOKE, who's allowed to open which drawer) and TCL (Transaction Control Language — COMMIT, ROLLBACK, "did this whole filing operation actually go through, or should I undo it").

Why the split matters

Keeping "what shape is this data" separate from "what is the data right now" is what lets a database enforce rules automatically instead of trusting every application developer to remember them. If you decide, once, at the cabinet level, that a folder must never be empty — that's a NOT NULL constraint — no filing clerk (no line of application code, ever, from any service that touches this table) can accidentally break that rule. The rule lives in the structure, not in someone's memory.

The six constraints, and what each one is actually guarding against

Constraint	What it enforces	What breaks without it
`PRIMARY KEY`	Every row has a unique, non-null identifier	Two "different" rows become indistinguishable; you can't safely reference "this one row" from anywhere else
`FOREIGN KEY`	A reference always points to a row that actually exists	An order can point to a `customer_id` that was deleted — an "orphaned" record with no real owner
`UNIQUE`	No two rows share this value	Two users register with the same email; login becomes ambiguous
`NOT NULL`	This column always has a value	"Unknown" silently gets treated as zero, empty, or crashes downstream code that assumed a value
`CHECK`	A value must satisfy a business rule	`age = -5` or `price = -100` gets silently accepted
`DEFAULT`	A sensible value is used when none is given	Every single INSERT statement across every service has to remember to set it manually, or it's `NULL`

PostgreSQL 17 adds temporal tables, an improved MERGE, richer JSONB, and better partitioning — but the DDL/DML/constraint split above hasn't changed since the earliest days of SQL, and it won't. Everything else in this chapter is detail on top of that one idea: structure first, data second, rules enforced by the cabinet — not by whoever happens to be filing today.

When you run a DDL or DML statement, it doesn't take effect the instant you hit Enter — it passes through a small pipeline inside the database engine first. Knowing this pipeline is what separates "I can write SQL" from "I understand why this error message showed up."

DDL execution flow

Parser validates the SQL syntax and builds a parse tree — this is where a typo like CRATE TABLE gets caught, before the engine even thinks about your schema.
Analyzer resolves every identifier — does this table exist, does this column exist, is the data type valid?
Planner decides the execution strategy for the operation.
Executor actually performs it.
Catalog (PostgreSQL: pg_class, pg_attribute) gets updated — this is the database's own internal "table of tables," and it's how \d or information_schema queries know your table exists at all.
Transaction commit — DDL is auto-committed in most databases; PostgreSQL is unusual in that it wraps DDL in a transaction too, meaning you genuinely can ROLLBACK a CREATE TABLE in Postgres, which surprises engineers coming from MySQL.

DML execution

INSERT/UPDATE/DELETE go through a similar but distinct pipeline: Parser → Analyzer → Rewriter → Planner → Optimizer → Executor. The extra "Rewriter" step matters — it's where views and rules get expanded into their underlying real-table operations before the optimizer ever sees them.

What each DML statement actually does to storage:

INSERT — data is written to heap pages; every index on the table is updated to point at the new row.
UPDATE — in PostgreSQL's MVCC model, the old row version is marked dead (not overwritten in place); a new row version is written alongside it.
DELETE — the row is marked dead; the space isn't reclaimed immediately — VACUUM does that later.
WAL (Write-Ahead Log) — every one of these changes is recorded to the WAL before it's considered durable, which is the mechanism that lets the database recover correctly after a crash.

Here's the order a table actually moves through in a real project — not just the syntax for each command in isolation, but where each one shows up in a table's lifetime.

Create the database and schema — establish the logical namespace everything else will live inside.
CREATE TABLE with constraints — define the structure and the integrity rules together, from day one, not as an afterthought.
INSERT data — populate the table; every constraint you defined gets validated on every single row as it goes in.
UPDATE / DELETE — modify data over time; foreign keys get checked here too (ON DELETE actions decide what happens to dependent rows).
ALTER TABLE — add or modify columns and constraints as requirements change, without recreating the table from scratch.
TRUNCATE vs DELETE when you need to clear a table — TRUNCATE is DDL (fast, no row-by-row logging); DELETE is DML (slower, but revertible mid-transaction and fires triggers).
DROP TABLE — removes the table permanently; CASCADE also drops everything that depends on it, which is exactly as dangerous as it sounds in production.

Most schema pain shows up months after launch, when a table has real data in it and "just add a constraint" is no longer a five-second operation. Building these habits in from the first CREATE TABLE is far cheaper than retrofitting them later.

Always define a PRIMARY KEY on every table — every row needs a stable, unique identity.
Prefer SERIAL or IDENTITY (PostgreSQL 17) for surrogate primary keys over natural keys, which have an unpleasant habit of changing.
Add FOREIGN KEY constraints — enforce referential integrity at the database level, not just in application code that every future engineer has to remember to write correctly.
Use CHECK constraints for business rules directly in the schema: salary > 0, age BETWEEN 18 AND 120.
Specify NOT NULL deliberately wherever a value is genuinely required — silent null-handling bugs are one of the most common categories of production incident.
Prefer VARCHAR with an explicit max length over unbounded TEXT for columns you plan to index — it keeps index entries predictable in size.
Give columns sensible DEFAULT values (DEFAULT NOW(), DEFAULT TRUE) so application code doesn't have to set them on every insert path.
Think twice before CASCADE DELETE — it's convenient until someone deletes one row and silently takes a thousand related rows with it.
Use TIMESTAMPTZ (timestamp with time zone), not bare TIMESTAMP, the moment your users span more than one time zone.
In PostgreSQL, run DDL inside an explicit transaction when making multiple related schema changes — if step 3 of 5 fails, you want the whole migration to roll back, not leave the schema half-changed.

⚠️ Why these keep happening

None of the mistakes below cause an error message on day one. That's exactly what makes them dangerous — they pass code review, they pass the demo, and they surface three weeks (or three years) later as a "how did the data ever get like this?" incident, usually discovered by whoever is on call.

Using NULL as a business value. NULL means "unknown," not zero, not empty, not "not applicable." The moment application code starts treating a missing value as a business signal (e.g. NULL discount = "no discount"), every future SUM() or AVG() over that column silently produces a wrong answer, because SQL's three-valued logic excludes NULL from most aggregates without warning.
Forgetting ON DELETE on a FOREIGN KEY. The default, RESTRICT, blocks the delete entirely — which surprises engineers who expected CASCADE or SET NULL and instead get a confusing constraint-violation error in production.
VARCHAR with no length limit, used as if it were TEXT. It works fine in dev with ten test rows. It becomes a schema-migration headache the day someone tries to add a real length limit to a column with five million rows already in it.
Using FLOAT for money. Floating point can't represent most decimal fractions exactly — 0.1 + 0.2 is not exactly 0.3 in floating point. For anything involving currency, DECIMAL/NUMERIC is not a style preference, it's a correctness requirement.
No DEFAULT on a nullable column. Every nullable column without a default is a column every future INSERT statement, in every future service, has to remember to populate — and eventually, one won't.
DROP TABLE without IF EXISTS. Harmless in a script you run once by hand; it silently breaks an idempotent migration script that's supposed to be safely re-runnable.
TRUNCATE inside a transaction, assumed to be fully revertible. In MySQL specifically, TRUNCATE causes an implicit commit — the "just roll it back if something goes wrong" safety net you were counting on isn't actually there.
Adding a NOT NULL column to a large, live table the naive way locks the entire table for the duration of the rewrite in MySQL. PostgreSQL 11+ instead avoids the rewrite entirely for a constant default — but only if you know to rely on that behavior rather than accidentally defeating it.

Most of these only matter once your tables stop being toy-sized — but knowing them before you need them means you reach for the right tool immediately instead of debugging a slow migration under pressure.

TRUNCATE is roughly 100x faster than DELETE for clearing an entire table — it skips row-by-row logging entirely, which DELETE cannot avoid.
Use COPY (PostgreSQL) or LOAD DATA INFILE (MySQL) for bulk inserts — both bypass large parts of the normal per-row INSERT overhead.
SET CONSTRAINTS ALL DEFERRED defers constraint checking to transaction commit, which lets you load interdependent data in any order instead of fighting foreign-key ordering.
Partial indexes on filtered tables — CREATE INDEX ON orders(status) WHERE status = 'PENDING' — index only the rows you actually query often, keeping the index small and fast.
UNLOGGED tables (PostgreSQL) skip WAL overhead entirely for temporary staging data — 3-5x faster writes, at the cost of not surviving a crash, which is fine for data you can regenerate.
Batch your INSERTs: INSERT INTO ... VALUES (...), (...), (...) — roughly 100 rows per statement is a good default before returns diminish.
pg_dump/pg_restore for schema migration between environments is faster than replaying a long history of DDL scripts.

The difference between "I know DDL syntax" and "I can be trusted to run it against production" is almost entirely process, not SQL knowledge.

Use Flyway or Liquibase for schema migrations — versioned, auditable, and reversible, instead of someone's memory of what they ran last Tuesday.
Never run DDL directly against production — always through a migration tool with a review step.
Test constraint additions against a production-sized copy first — some operations that are instant on 100 rows take minutes and hold locks on 100 million.
Monitor table bloat with pg_stat_user_tables — a high dead-tuple count means VACUUM isn't keeping up.
Use CREATE INDEX CONCURRENTLY in PostgreSQL — builds the index without holding a table lock, at the cost of taking longer.
In MySQL, use pt-online-schema-change for DDL on large tables, for the same reason.
Document every constraint and the business reason behind it in a data dictionary — the engineer debugging a CHECK violation at 2am shouldn't have to guess why the rule exists.

🧪 Before you move on

Reading about constraints and reading about TRUNCATE vs DELETE is not the same as having watched them behave differently with your own eyes. These four exercises are short on purpose — the goal is muscle memory, not a big project.

Create the employees/departments schema from this chapter. Add a CHECK constraint that hire_date can never be in the future, and try to violate it on purpose.
Add a new column performance_rating (1–5) to the employees table with a CHECK constraint. Add a DEFAULT too, and verify it applies correctly to rows that already existed before the column was added.
Practice TRUNCATE vs DELETE directly: insert 1000 rows, time a DELETE of all of them, then reload and time a TRUNCATE instead. Observe the difference yourself rather than taking it on faith.
Write a migration script using a proper transaction: BEGIN; ALTER TABLE ...; CREATE INDEX CONCURRENTLY ...; COMMIT; — then note why CREATE INDEX CONCURRENTLY specifically must run outside a transaction block, and what error you get if you try it inside one.

✓ Quick recap

DDL (CREATE, ALTER, DROP, TRUNCATE) defines structure; DML (SELECT, INSERT, UPDATE, DELETE) manipulates data — both are transactional in PostgreSQL, DDL is auto-committed in most other engines.
Constraints — PRIMARY KEY (unique + not null), FOREIGN KEY (referential integrity), UNIQUE, NOT NULL, CHECK, DEFAULT — exist so the database enforces rules automatically, instead of trusting every line of application code to remember them.
TRUNCATE vs DELETE: TRUNCATE is DDL, minimal logging, effectively instant regardless of table size; DELETE is DML, row-logged, revertible mid-transaction, and supports a WHERE clause.
Use DECIMAL/NUMERIC for money, always; TIMESTAMPTZ for anything global; SERIAL/IDENTITY for auto-incrementing surrogate keys.
ON DELETE options change everything about how deletes ripple through related tables: CASCADE (delete children too), SET NULL, RESTRICT (block the delete).
Zero-downtime DDL on large, live tables is possible but engine-specific: PostgreSQL 11+ gives instant DEFAULT additions; MySQL needs pt-online-schema-change for the same result.

Want a visual for this concept?

Generate a diagram tailored to “SQL Fundamentals — DDL, DML & Constraints” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to SELECT Queries — WHERE, GROUP BY, HAVING, ORDER BY →← Back to all SQL chapters