SQL can define a database's structure as well as query its data. Statements that create or change that structure are commonly called data definition language (DDL).

Creating tables

CREATE TABLE declares columns, their types and constraints:

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    team_id BIGINT REFERENCES teams(id)
);

The schema therefore records both representation and rules that stored data must obey.

Changing a schema

ALTER TABLE changes an existing table: columns or constraints can be added, removed or modified. DROP TABLE removes a table and must be used with care because it can destroy stored data.

Defaults can provide values for omitted columns, while NOT NULL, UNIQUE, CHECK, primary keys and foreign keys enforce invariants at the database boundary.

Schema operations versus data operations

Statements such as SELECT, INSERT, UPDATE and DELETE operate on rows. DDL changes the structure in which those rows are stored.

Knowing this distinction makes schema evolution explicit: a migration is not a special kind of database magic, but an ordered application of schema and data transformations.