Creating and changing tables: DDL
Stage 1 manipulated data with DML. DDL (Data Definition Language) is the other half: it defines and reshapes the structure itself - tables, columns, constraints. Three commands do the work: CREATE makes objects, ALTER changes them, DROP removes them. DDL manipulates the schema; it does not edit row data the way UPDATE does.
The boxes below run real SQLite in your browser - Reset data restores the original store.
CREATE TABLE
CREATE TABLE defines a table's columns, their types, and the constraints that keep data valid. Add IF NOT EXISTS to avoid an error when it already exists.
Constraints - the guardrails
Constraints are rules the database enforces on every write, so bad data cannot get in. They are the cheapest, most reliable data quality you will ever add.
| Constraint | Guarantees |
|---|---|
NOT NULL | the column must always have a value |
UNIQUE | no two rows share the value |
PRIMARY KEY | unique and not null - the row's identity |
FOREIGN KEY (REFERENCES) | the value must exist in another table |
CHECK (...) | the value must satisfy a condition |
DEFAULT x | use x when none is supplied |
The CHECK above forbids a negative balance. Watch it reject a bad insert:
For an auto-incrementing key, modern PostgreSQL favours id INTEGER GENERATED ALWAYS AS IDENTITY over the older SERIAL type - it is the SQL-standard syntax and avoids SERIAL's ownership quirks. (SQLite just uses INTEGER PRIMARY KEY, which auto-assigns.)
Generated columns - computed, not stored by hand
A generated column derives its value from other columns with a formula the database evaluates for you. You never write to it; you cannot get it out of sync. Declare it with GENERATED ALWAYS AS (expression).
Two flavours: STORED computes the value on write and saves it on disk; VIRTUAL computes it on read and saves nothing. Use STORED when reads are frequent or you want to index the column; VIRTUAL when writes dominate and space matters.
subtotal is always unit_price * quantity - the database recomputes it whenever either input changes. This is the safe way to keep a derived value: it cannot drift the way a hand-maintained copy can (the denormalization trap you will meet in normalization).
Generated columns are SQL-standard and supported by SQLite, PostgreSQL, MySQL, and others. PostgreSQL currently implements only STORED; SQLite and MySQL support both STORED and VIRTUAL.
Foreign keys: what happens on delete
A foreign key blocks you from orphaning rows - but blocking is only one option. The referential action decides what happens to the children when a referenced parent row is deleted (or its key updated). You set it with ON DELETE (and ON UPDATE):
| Action | On deleting a referenced parent row |
|---|---|
RESTRICT / NO ACTION | refuse the delete while children exist (the safe default) |
CASCADE | delete the children too, automatically |
SET NULL | keep the children, blank out their FK column |
SET DEFAULT | set the children's FK to its column default |
Choosing well is a modeling decision:
CASCADEwhen the child cannot exist without the parent - delete an order, itsorder_itemsshould go with it.SET NULLwhen the child outlives the parent - delete an author, keep their posts but show no author. (The FK column must be nullable.)RESTRICTwhen deletion should be a deliberate act - refuse to delete a customer who still has orders, forcing you to handle them first.
Watch ON DELETE CASCADE clean up children automatically:
A single DELETE can sweep away whole trees of rows through chained cascades, with no second prompt. It is the right tool for true parent-child ownership, but reach for RESTRICT whenever a stray delete would be a disaster.
Tables from queries: snapshot vs view
You can also build a table from a query. There are two flavours, and the difference matters.
- Snapshot -
CREATE TABLE ... AS SELECT ...copies the rows as they are now into a new real table. A point-in-time copy, frozen. Good for "state before a migration" or historical analytics. - View -
CREATE VIEW ... AS SELECT ...stores the query, not the rows. It behaves like a virtual table and is always live - query it and you see current data. Good for reusing a query shape without duplicating data.
A snapshot is a real table (frozen data); a view is a saved query (live). Together with CTEs, they are your toolkit for naming and reusing result sets - a CTE lasts one statement, a view lasts until you drop it, a snapshot persists its data.
ALTER TABLE
ALTER TABLE reshapes an existing table - most commonly adding a column:
You can also RENAME COLUMN, RENAME TO (rename the table), and DROP COLUMN.
SQLite's ALTER is deliberately limited: it cannot change a column's type or add a constraint to an existing table - you rebuild the table instead. PostgreSQL, SQL Server, and others support the fuller form, e.g. ALTER TABLE t ALTER COLUMN c TYPE INTEGER or ALTER TABLE t ADD CONSTRAINT .... Try the SQLite-only forms above; the type-change form would error here.
ALTER briefly locks the object while it runs, so on a busy production table, schedule structural changes for quiet periods.
DROP, TRUNCATE, DELETE - choosing the right eraser
These three all remove things, at very different scopes:
DROP TABLE t | TRUNCATE TABLE t | DELETE FROM t WHERE ... | |
|---|---|---|---|
| Removes | the whole table + data | all rows, keeps the table | only matching rows |
| Kind | DDL | DDL/DML | DML |
| Speed | fast | fast, even on huge tables | slower, row by row |
| Triggers | dropped with table | usually skipped | run per row |
| Auto-key sequence | gone | reset | unchanged |
DELETE is the only one that takes a WHERE and removes some rows. TRUNCATE empties a table fast but is all-or-nothing (and SQLite has none - use DELETE FROM t). DROP removes the table itself.
Plain DROP TABLE fails if other objects depend on the table. DROP TABLE ... CASCADE removes the table and every dependent object (views, foreign keys, more) in one irreversible sweep. Reach for it only when you are certain - in production, renaming and archiving beats dropping.
Quick quiz
DDL: CREATE, ALTER, DROP
7 questions1What do DDL commands do?
2Which constraint limits the values allowed in a column to those passing a condition?
3Which is NOT a valid use of ALTER?
4You want to remove all rows but keep the empty table and its structure. Which fits best?
5Why use a STORED generated column for subtotal = unit_price * quantity instead of computing it in the app?
6You delete an order; its order_items should disappear too. Which referential action fits?
7Why is DROP TABLE ... CASCADE more dangerous than DROP TABLE?
Now the design half of the stage: what schema to build. Modeling with ER diagrams turns requirements into entities, relationships, and keys.