Create tables and constraints

Why this matters

Yesterday's sketch becomes real today. By the end of the hour you will have two tables inside a running PostgreSQL server, and — more importantly — you will have watched the database refuse rows that break your rules. That refusal is the point. A rule enforced only in your Express validation code holds until someone writes a script, runs a manual fix, or forgets a branch. A rule declared in the schema holds against everything.

Connecting with psql

PostgreSQL is the database server: a program that runs in the background and answers queries. psql is the terminal client you type SQL into. They are separate programs, exactly like your browser and a web server.

First confirm the server is up, then create a database — a named container for one project's tables:

pg_isready
createdb maintenance
psql maintenance

/tmp:5432 - accepting connections
psql (18.4 (Homebrew))
Type "help" for help.

maintenance=#

createdb prints nothing when it works; silence is success. maintenance=# is the psql prompt, naming the database you are connected to. Inside psql, commands beginning with a backslash are psql's own, not SQL:

• \l — list databases
• \dt — list tables in this database
• \d equipment — describe one table: columns, types, and constraints
• \q — quit

SQL statements, by contrast, must end with a semicolon. Forgetting it is the most common beginner snag: psql shows a maintenance-# continuation prompt and waits, apparently frozen. It is not frozen — type ; and press Enter.

psql: error: connection to server at "localhost" (::1), port 5432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?

CREATE TABLE

CREATE TABLE declares a table: its name, and one line per column giving the column's name, its type, and any rules attached to it.

CREATE TABLE equipment (
  id            integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name          text NOT NULL,
  serial_number text NOT NULL UNIQUE,
  installed_on  date NOT NULL
);

Read one line: serial_number is the column name, text is its type, and NOT NULL UNIQUE are two constraints. SQL keywords are conventionally uppercase and identifiers lowercase — the language ignores the case, but the convention makes structure visible at a glance.

GENERATED ALWAYS AS IDENTITY tells PostgreSQL to supply the id itself, counting up, so you never provide one. That is how a surrogate key gets its values. (Older code and tutorials use serial for the same job. Identity columns are the current standard; prefer them.)

Choosing data types

The type decides what values a column may hold and what operations make sense on it. These five cover almost everything this week:

Three notes that save real pain. Use text rather than varchar(50) in PostgreSQL: there is no performance benefit to the length limit, and picking a maximum length you later regret means a schema change. Use numeric rather than a floating-point type for anything you will add up, because numeric stores exact decimals. And always use timestamptz rather than timestamp — the version without a time zone silently loses the information needed to compare two moments recorded in different places.

Constraints: rules the database enforces

A constraint is a rule attached to a column or table. Every write is checked against it, and a row that violates it is rejected with an error — the row is not written, not partly written, not written with a warning.

• NOT NULL — this column must have a value. NULL means "no value here at all"; it is not an empty string or zero.
• UNIQUE — no two rows may hold the same value in this column. It prevents duplicate values in the constrained key. It does not forbid NULL: several rows may each be NULL, because NULL means "unknown" and two unknowns are not known to be equal. If a column must be both present and unique, declare NOT NULL UNIQUE.
• CHECK (condition) — a condition that must be true for every row. CHECK (status IN ('operational', 'maintenance', 'retired')) restricts a text column to a fixed set of values.
• PRIMARY KEY — the row's identifier. It is NOT NULL and UNIQUE together, plus the declaration that this is what other tables point at.
• REFERENCES other_table(column) — a foreign key. The value must match an existing row over there, or the write fails.
• DEFAULT value — not a constraint, but it belongs here: the value used when you do not supply one.

Walkthrough: build the schema, then try to break it

Create db/schema.sql in your Week 6 project folder and put both tables in it.

CREATE TABLE equipment (
  id            integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name          text NOT NULL,
  serial_number text NOT NULL UNIQUE,
  status        text NOT NULL DEFAULT 'operational'
                CHECK (status IN ('operational', 'maintenance', 'retired')),
  installed_on  date NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE maintenance_records (
  id           integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  equipment_id integer NOT NULL REFERENCES equipment(id) ON DELETE CASCADE,
  performed_on date NOT NULL,
  hours_spent  numeric(5,2) NOT NULL CHECK (hours_spent > 0),
  notes        text NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);

ON DELETE CASCADE answers a question the database will otherwise ask you later: if a piece of equipment is deleted, what happens to its maintenance records? CASCADE means they are deleted too. Order matters — equipment must be created before the table that references it.

Run the file, then look at what you got:

psql maintenance -f db/schema.sql

CREATE TABLE
CREATE TABLE

psql maintenance -c '\d equipment'

    Column     |           Type           | Nullable |           Default
---------------+--------------------------+----------+------------------------------
 id            | integer                  | not null | generated always as identity
 name          | text                     | not null |
 serial_number | text                     | not null |
 status        | text                     | not null | 'operational'::text
 installed_on  | date                     | not null |
 created_at    | timestamp with time zone | not null | now()
Indexes:
    "equipment_pkey" PRIMARY KEY, btree (id)
    "equipment_serial_number_key" UNIQUE CONSTRAINT, btree (serial_number)
Check constraints:
    "equipment_status_check" CHECK (status = ANY (ARRAY['operational'::text, ...]))

That is the schema reading itself back to you — the closest thing to evidence a database offers. Now insert one good row, then four bad ones.

INSERT INTO equipment (name, serial_number, status, installed_on)
VALUES ('Feed Pump 3', 'PMP-0003', 'operational', '2021-06-14');

INSERT 0 1

INSERT 0 1 means one row was inserted. Now the failures — run each and read the error properly.

INSERT INTO equipment (name, serial_number, installed_on)
VALUES ('Copy of Pump 3', 'PMP-0003', '2022-01-01');

ERROR:  duplicate key value violates unique constraint "equipment_serial_number_key"
DETAIL:  Key (serial_number)=(PMP-0003) already exists.

INSERT INTO equipment (name, serial_number, status, installed_on)
VALUES ('Chiller 1', 'CHL-0001', 'broken', '2020-03-02');

ERROR:  new row for relation "equipment" violates check constraint "equipment_status_check"
DETAIL:  Failing row contains (3, Chiller 1, CHL-0001, broken, 2020-03-02, ...)

INSERT INTO equipment (name, serial_number, installed_on)
VALUES (NULL, 'CHL-0002', '2020-03-02');

ERROR:  null value in column "name" of relation "equipment" violates not-null constraint

INSERT INTO maintenance_records (equipment_id, performed_on, hours_spent, notes)
VALUES (999, '2024-01-01', 1.5, 'ghost record');

ERROR:  insert or update on table "maintenance_records" violates foreign key constraint
        "maintenance_records_equipment_id_fkey"
DETAIL:  Key (equipment_id)=(999) is not present in table "equipment".

Four rules, four rejections, each error naming the constraint that stopped it. That name is why constraints are worth declaring: the database tells you exactly which rule you broke.

Your turn

Produce db/schema.sql plus notes recording which invalid rows were rejected and why.

1. Create the database with createdb maintenance and connect with psql maintenance.
2. Write db/schema.sql from your Day 43 sketch. Include equipment and maintenance_records with the constraints above. Do not copy blindly — every constraint should trace to a line you wrote on paper yesterday.
3. Load it with psql maintenance -f db/schema.sql and confirm both tables exist with \dt.
4. Run \d maintenance_records and find your foreign key in the output.
5. Insert three valid equipment rows. Each should print INSERT 0 1.
6. Attempt four invalid rows: a duplicate serial number, an unlisted status, a missing name, and a maintenance record for equipment id 999. Copy each error's first line into db/notes.md with one sentence on which constraint fired.
7. Add one constraint of your own choosing — for example CHECK (installed_on <= CURRENT_DATE) on a new table — and prove it rejects a row.

Common pitfalls

• Forgetting the semicolon. psql shows maintenance-# and waits. It is not hung; finish the statement.
• Single vs double quotes. SQL text values take single quotes: 'PMP-0003'. Double quotes mean an identifier, so "PMP-0003" produces ERROR: column "PMP-0003" does not exist.
• Creating the child table first. REFERENCES equipment(id) fails if equipment does not exist yet. Parent tables come first in the file.
• Assuming UNIQUE implies NOT NULL. It does not. Multiple NULLs are allowed in a unique column; declare both when you mean both.

Verify it yourself

Open today's reference, the PostgreSQL tutorial, and read its chapters on creating a table and on constraints.

1. The tutorial lists constraint types this lesson skipped, including EXCLUDE. Find one and write down, in your own words, a case where it would be the right tool.
2. This lesson claimed a UNIQUE column still accepts multiple NULL values. Find the documentation's statement on that and record whether it agrees.

Add both answers to db/notes.md. Tomorrow you stop writing rows and start asking questions of them.