CREATE TABLE: a constraint is a test that runs on every write

The table is the first line of defence

Every check in the previous module ran after the data was in. A constraint runs before, on every insert and every update, and a row that fails it never exists. That is the difference between finding a negative payment in next month’s audit and never having one.

CREATE TABLE payments (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  reference    text        NOT NULL,
  student_id   bigint      NOT NULL REFERENCES students (id),
  amount       numeric(12, 2) NOT NULL,
  paid_on      date        NOT NULL,
  method       text        NOT NULL,
  note         text,
  created_at   timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT payments_reference_unique  UNIQUE (reference),
  CONSTRAINT payments_amount_positive   CHECK (amount > 0),
  CONSTRAINT payments_method_known      CHECK (method IN ('cash', 'upi', 'card', 'transfer')),
  CONSTRAINT payments_not_in_future     CHECK (paid_on <= current_date)
);

Read it as a list of promises. Every payment has a reference and it is unique. Every payment belongs to a student who exists. The amount is money, to the paisa, and positive. The method is one of four. The date is not in the future. note is the only thing allowed to be missing, and that is a decision, not an omission.

What each kind refuses

NOT NULL refuses an unknown. Pair it with DEFAULT for columns that should be filled in automatically, as created_at is; a NOT NULL column with no default and no value in the INSERT is an error, which is what you want for amount and not for created_at.

UNIQUE refuses a repeat. It builds an index to do it, which the performance module returns to, and it treats NULLs as distinct from each other, so a nullable unique column can hold many NULLs. PRIMARY KEY is UNIQUE plus NOT NULL plus the declaration that this is the identity, as the keys lesson described.

CHECK refuses any row for which its expression is not true. It can reference several columns of the same row: CHECK (valid_to IS NULL OR valid_to > valid_from) is the guard for every effective-dated table in the time module. It cannot look at other rows or other tables, and that limit matters below.

REFERENCES refuses a row that points at nothing, and refuses (or cascades) the deletion of a row that is pointed at.

Name them

An unnamed constraint produces an error like new row violates check constraint "payments_check1". A named one says payments_amount_positive, and the person reading the error at two in the morning knows what happened without opening the schema. Names cost nothing.

The rule a CHECK cannot express

“A student may not have more than one payment per day” is a rule about two rows. “A booking may not overlap another booking of the same room” is a rule about two rows. CHECK sees one row at a time and cannot enforce either. The options, in order of preference:

A UNIQUE constraint expresses “at most one row per combination”: UNIQUE (student_id, paid_on) enforces the first rule exactly. Postgres’s EXCLUDE constraint expresses “no two rows may overlap”, which is the second: EXCLUDE USING gist (room_id WITH =, during WITH &&) on a range column, and two bookings for the same room whose ranges intersect cannot both exist. Beyond those, the rule needs a trigger, which is code that runs on every write and can query anything, or it lives in the application, which means every application. A rule that lives in the database is enforced for the admin fixing data by hand at midnight; a rule in the application is not.

The engines differ in what they enforce

SQLite accepts a REFERENCES clause and, by default, ignores it. Foreign keys are enforced only when the connection has run PRAGMA foreign_keys = ON, and every connection has to do it. A SQLite table with foreign keys declared and never enforced is the norm, not the exception, and orphan rows are the usual discovery. MySQL’s older MyISAM engine has the same property; InnoDB enforces. DuckDB enforces primary and unique keys and checks, and is built for analysis rather than for a thousand concurrent writers, which is the right trade for what it is.

Constraints are cheap to add and expensive to lack

A constraint costs a comparison per write. A missing constraint costs the query that finds the bad rows a year later, the decision about what to do with them, and every report that ran in between. When in doubt, add it. When the data already violates it, Postgres refuses to add it, which is itself the list of rows to fix, and NOT VALID lets you add it for future rows while you work through the old ones.