03 — DDL: Shaping the Tables Themselves

"Just the setup script" was how I filed DDL and then forgot it, which made schema changes feel riskier than they are. Writing it down separated one idea from the day-to-day verbs: DDL is the SQL subset that changes the shape of the database itself — creating, altering, emptying, and dropping tables and other structures — not the rows inside them. [1][2]

The framing that clicked is the split between defining and using. The four CRUD verbs operate on data within a structure that already exists. DDL is what brings that structure into being, reshapes it, or removes it. CREATE TABLE defines the columns and types; after that, INSERT and SELECT have something to work against. Mess with DDL and you're editing the stage, not the actors.

CREATE TABLE — define the shape

CREATE TABLE brings a table into existence with its name, columns, types, and constraints [1][2]:

CREATE TABLE customers (
  id          INTEGER PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(255) UNIQUE,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This is the contract every later INSERT and SELECT obeys. The column types (INTEGER, VARCHAR, TIMESTAMP) decide what fits; the constraints (PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT) decide what's allowed. I think of CREATE TABLE as writing the rules the data has to live by.

ALTER TABLE — change an existing shape

ALTER TABLE modifies a table that already exists — adding, dropping, or renaming columns, changing types, or attaching constraints [3][4]:

ALTER TABLE customers ADD COLUMN phone VARCHAR(20);
ALTER TABLE customers DROP COLUMN deprecated_field;

This is DDL's real superpower and its real risk: it reshapes a structure that may already hold millions of rows. Adding a nullable column is cheap; changing a column's type or adding a NOT NULL constraint can trigger a full table rewrite. In production I treat every ALTER as a migration to sequence carefully, not a one-off command.

DROP TABLE — remove the structure

DROP TABLE deletes the table and all its data, permanently [5]:

DROP TABLE customers;

There's no WHERE, no undo. Once dropped, the structure and every row are gone unless a backup exists. I treat DROP the way I treat rm -rf — read the statement twice, and confirm the table name.

TRUNCATE TABLE — empty it, fast

TRUNCATE TABLE removes all rows but keeps the table structure intact [6][7]:

TRUNCATE TABLE staging_imports;

The distinction from DELETE FROM staging_imports; is the part I had to nail down. DELETE is a DML operation that logs every row removal, so it's safe, transactional, and slow for big clears. TRUNCATE is DDL — it deallocates the data pages wholesale, skipping per-row logging, which is why it's dramatically faster for wiping a table [6]. The cost: in many engines TRUNCATE can't be rolled back inside a transaction the way DELETE can. Reach for it on staging and log tables, not on anything where you might want the rows back.

How I use this

The habit I keep from DDL is a mental category check before I run a statement: am I changing the shape (DDL — CREATE, ALTER, DROP, TRUNCATE) or the contents (DML — INSERT, UPDATE, DELETE)? If it's DDL on a live table, I write it as a versioned migration, never ad-hoc, because schema changes are the one class of edit that's hardest to roll back. And for bulk clears I reach for TRUNCATE only on disposable tables — anything transactional gets DELETE, accepting the slowness as the price of safety.

References

[1] dbt Labs, "Data Definition Language (DDL)," docs.getdbt.com, 2024. [Online]. Available: https://docs.getdbt.com/terms/ddl

[2] DbVisualizer, "The Definitive Guide on Data Definition Language," dbvis.com, 2024. [Online]. Available: https://www.dbvis.com/thetable/sql-ddl-the-definitive-guide-on-data-definition-language/

[3] TechOnTheNet, "ALTER TABLE Statement," techonthenet.com, 2024. [Online]. Available: https://www.techonthenet.com/sql/tables/alter_table.php

[4] PostgreSQL Tutorial, "ALTER TABLE," postgresqltutorial.com, 2024. [Online]. Available: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-alter-table/

[5] Coginiti, "Drop a Table," coginiti.co, 2024. [Online]. Available: https://www.coginiti.co/tutorials/beginner/drop-a-table/

[6] TutorialsPoint, "TRUNCATE TABLE," tutorialspoint.com, 2024. [Online]. Available: https://www.tutorialspoint.com/sql/sql-truncate-table.htm

[7] Programiz, "SQL CREATE TABLE," programiz.com, 2024. [Online]. Available: https://www.programiz.com/sql/create-table