DDL & DML

Define tables with constraints (CREATE, ALTER, DROP), change rows safely (INSERT, UPDATE, DELETE, upsert), and change a live schema without locking it up.

Overview

SQL statements come in two main groups. DDL (data definition language) changes the shape of the database: tables, columns, constraints and indexes. DML (data manipulation language) changes the rows inside those tables.

DDL — CREATE, ALTER, DROP, TRUNCATE — the schema, or all rows at once
DML — INSERT, UPDATE, DELETE, MERGE — individual rows
Queries — SELECT — nothing; it only reads

In interviews this topic shows up as schema design ('design the tables for an online store'), as small data-fix problems ('delete the duplicate emails'), and as operations questions ('add a NOT NULL column to a busy table without downtime').

Create tables with constraints

A constraint is a rule the database itself checks on each insert and update, so no program, script or manual fix can store rows that break it.

PRIMARY KEY — unique and not null; builds a unique index — one per table
UNIQUE — no two equal values — allows many NULLs by default
NOT NULL — a value is required — none
CHECK (expr) — expr must not be false — NULL makes expr unknown, which passes
REFERENCES (foreign key) — the value must exist in the parent table — the child column gets no index automatically
DEFAULT — value used when an INSERT leaves the column out — not a rule; it never rejects anything

GENERATED ALWAYS AS IDENTITY is the standard way to make an auto-numbered key. The column gets its own sequence and is NOT NULL, and an INSERT cannot set it by accident. In PostgreSQL, text and varchar(n) perform the same (varchar(n) only adds a length check on write), so a length limit is a business rule, not a speed trick.

Constraints and NULL

NULL breaks two rules people expect. A CHECK constraint passes when its expression is NULL, and a UNIQUE constraint lets any number of rows hold NULL, because two NULLs are not equal. Add NOT NULL when a value is required.

Both rows went in: neither the CHECK nor the UNIQUE rule stopped them. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT for when at most one NULL is allowed.

Foreign key actions

When a parent row is deleted or its key changes, the foreign key's action decides what happens to the child rows.

NO ACTION (the default) — the delete fails if children exist; the check can be deferred to the end of the transaction
RESTRICT — the delete fails at once; it cannot be deferred
CASCADE — the children are deleted too
SET NULL / SET DEFAULT — the children's key is set to NULL or its default

2 — 1

CASCADE removed Ana's two orders with her. That is right for order lines under an order, and risky for anything you must keep, such as invoices. Also index the child column (orders_customer_idx above): PostgreSQL does not create that index for you, and without it every parent delete scans the child table.

Write rows: INSERT, UPDATE, DELETE

Each DML statement touches the rows its WHERE clause matches. PostgreSQL adds three useful extras.

RETURNING — gives back the inserted, updated or deleted rows, such as a new id
INSERT ... ON CONFLICT — an upsert: insert, or on a unique clash do nothing or update the existing row
UPDATE ... FROM and DELETE ... USING — join another table to decide which rows to change

3 — cy@x.com

An upsert is atomic: under concurrency, exactly one of the insert or the update happens. EXCLUDED names the row you tried to insert.

/home — 2

Before any UPDATE or DELETE, run the same WHERE clause as a SELECT and check the rows. A missing WHERE changes every row in the table.

Delete duplicates, keep one

A classic data-fix problem: a table holds the same email more than once, and you must keep only the row with the smallest id. Join the table to itself and delete every row that has a twin with a smaller id.

1 — a@x.com
2 — b@x.com

After the cleanup, add UNIQUE (email) so the duplicates cannot come back.

Transactions around changes

Group statements that must succeed together in a transaction. In PostgreSQL even DDL is transactional, so a failed migration can roll back a CREATE TABLE or an ALTER. MySQL does not work this way: CREATE, ALTER and DROP commit the open transaction before they run.

The rollback undid both the new column and the update.

Indexes: speed for reads, cost for writes

A B-tree index, the default kind, is a sorted copy of some columns that points back to the rows. It makes lookups and range scans fast. Every INSERT, UPDATE and DELETE must then update each index too.

status = 'paid' — yes, it is the leading column
status = 'paid' AND created_at > ... — yes, both columns
created_at > ... only — poorly: the whole index must be read, so the planner usually scans the table

On a busy production table, a plain CREATE INDEX blocks writes until it finishes. CREATE INDEX CONCURRENTLY lets writes continue; it takes longer and cannot run inside a transaction block.

Change a live table safely

Most ALTER TABLE forms take PostgreSQL's strongest lock, ACCESS EXCLUSIVE, which blocks reads and writes while they run. The danger is a long table scan or rewrite held under that lock. Some forms are quick, some are not.

ADD COLUMN with no default, or a non-volatile default such as a constant — metadata only, no rewrite
ADD COLUMN with a volatile default such as random() — rewrites the table and its indexes
Changing a column's type — usually rewrites the table
SET NOT NULL — scans the whole table, unless a valid CHECK already proves no NULLs
ADD CONSTRAINT ... NOT VALID, then VALIDATE CONSTRAINT — the add is instant; the validate scans under a lighter lock that allows writes

On a large table, run the backfill in batches of a few thousand rows so no single transaction holds locks for long.

DELETE, TRUNCATE or DROP

DELETE FROM t WHERE ... — matching rows — row by row; concurrent work continues — yes
TRUNCATE t — all rows — fast, frees space at once; ACCESS EXCLUSIVE lock — no
DROP TABLE t — the table itself — removes data, indexes and constraints — no

What you will be able to do

Write CREATE TABLE statements whose constraints keep bad data out, including the NULL cases.
Choose a foreign key action and say why the child column needs an index.
Write an upsert, an UPDATE with a join, and a duplicate-removal DELETE.
Plan a schema change on a busy table without a long lock.

When to use it

Designing tables for a new feature, with keys and constraints
Adding or removing columns, constraints or indexes on existing tables
Inserting, updating or deleting rows, including upserts
Cleaning up bad data, such as duplicate rows
Adding indexes for slow queries
Planning schema migrations that must not lock a busy table

More Databases & SQL topics

Start free on Cariara · Coding problems · System design studio