Use constraints and foreign keys

Use NOT NULL, UNIQUE, CHECK, primary keys, and foreign keys to reject invalid states.

CREATE TABLE projects (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug text NOT NULL UNIQUE,
  name text NOT NULL
);

CREATE TABLE tasks (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  project_id bigint NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  title text NOT NULL,
  progress integer NOT NULL CHECK (progress BETWEEN 0 AND 100)
);

These rules apply to every writer: the web application, an import script, a database console, and next year’s replacement service. Application validation can produce friendlier messages, but it cannot provide the same final guarantee under concurrency.

NOT NULL says a value must exist. CHECK constrains values in a row. UNIQUE protects a candidate identifier across rows. A primary key is the table’s main non-null unique identity. A foreign key prevents a child row from pointing at a missing parent.

Remember that a CHECK expression that evaluates to NULL passes. If a value is both required and constrained, use NOT NULL and CHECK together.

Choose foreign-key delete behavior deliberately:

• RESTRICT or NO ACTION rejects a parent deletion while children exist.
• CASCADE deletes dependent children too.
• SET NULL preserves the child but requires a nullable relationship.

CASCADE fits data that has no meaning alone, such as a task inside a deleted project. It is dangerous when one parent has millions of children or when the child represents an independent business record. Preview the affected rows and keep destructive operations observable.

PostgreSQL automatically indexes primary and unique keys, but it does not automatically index the referencing side of every foreign key. Add an index on tasks(project_id) when joins, parent deletion checks, or lookups need it.

Try it: attempt an invalid progress value, a duplicate slug, and a task with a missing project. Read each error and identify which invariant stopped the write.