--- title: "03 — DDL: Shaping the Tables Themselves" uid: ddl-shaping-tables tags: ["sql", "roadmap:sql", "alter", "truncate", "create", "ddl", "drop", "schema"] excerpt: "DDL is the SQL subset that changes the shape of the database itself — creating, altering, dropping structures — not the rows inside them." date: 2026-08-13T03:27:38+0000 source: https://www.aveshina.my.id/en/blog/ddl-shaping-tables --- "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. ```figure ``` ## 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](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/](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](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/](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/](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](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](https://www.programiz.com/sql/create-table) ```quiz Q: Which SQL subset changes the structure of the database rather than the rows in it? - DML (INSERT/UPDATE/DELETE) - DDL (CREATE/ALTER/DROP/TRUNCATE) correct: 1 explain: DDL defines and modifies the schema — tables, columns, types. DML operates on the data within an already-defined structure. Q: What's the key difference between DELETE and TRUNCATE? - There is no difference; they are aliases - DELETE logs each row removal (slow, transactional); TRUNCATE deallocates pages wholesale (fast, less rollback-friendly) correct: 1 explain: TRUNCATE skips per-row logging by dropping data pages, making it much faster for clearing a table, at the cost of transactional rollback semantics in many engines. Q: What does DROP TABLE do? - Removes all rows but keeps the table structure - Removes the table structure and all its data permanently correct: 1 explain: DROP TABLE deletes the table object entirely. TRUNCATE is the one that empties rows but keeps the structure. Q: Adding a NOT NULL column to a large existing table is risky because… - it can require a full table rewrite to backfill every row - SQL doesn't allow altering tables that contain data correct: 0 explain: ALTER TABLE operations that change types or add constraints may force the engine to rewrite the whole table, which is expensive on large tables. Q: Which command would you use to add a phone column to an existing customers table? - ALTER TABLE customers ADD COLUMN phone VARCHAR(20) - CREATE TABLE customers (phone VARCHAR(20)) correct: 0 explain: ALTER TABLE modifies an existing table. CREATE TABLE would attempt to create a brand-new table and fail if one already exists. ```