Constraints and Data Integrity

Data quality degrades gradually when the rules for what is valid live only in application code. Code gets bypassed. Migration scripts skip validation. A developer runs a one-off fix against the database directly. Each shortcut is small, and each one leaves behind a little corruption.

The most reliable way to enforce data quality is to make it structurally impossible to store invalid data. Constraints are rules you declare in the schema itself. The database enforces them on every INSERT, UPDATE, and DELETE, regardless of where the write comes from.

NOT NULL

The simplest and most commonly used constraint. It prevents a column from being left empty:

CREATE TABLE users (
  id     BIGINT PRIMARY KEY,
  email  TEXT NOT NULL,
  name   TEXT            -- nullable: name is optional
);

A column without an explicit constraint is nullable by default. Be intentional about which columns you leave nullable: every nullable column is a place where NULL will eventually appear and need to be handled.

UNIQUE

No two rows can have the same value in the constrained column:

email TEXT NOT NULL UNIQUE

You can also constrain combinations of columns:

UNIQUE (user_id, product_id)

This allows the same user_id to appear in multiple rows, and the same product_id to appear in multiple rows. What it prevents is the same (user_id, product_id) pair appearing twice. This is the standard constraint for a favorites or bookmarks table, where a user can save many products but not save the same product twice.

A UNIQUE constraint automatically creates a B-tree index on the constrained column(s).

CHECK

A custom condition that every row must satisfy:

CREATE TABLE products (
  id      BIGINT PRIMARY KEY,
  price   NUMERIC CHECK (price >= 0),
  status  TEXT    CHECK (status IN ('active', 'draft', 'archived'))
);

The database evaluates the check expression on every INSERT or UPDATE. Any row that would make the expression false is rejected with an error.

You can name constraints to get clearer error messages:

CONSTRAINT price_non_negative CHECK (price >= 0)

PRIMARY KEY

A primary key declares the column (or combination of columns) that uniquely identifies each row. It implicitly adds both NOT NULL and UNIQUE.

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

GENERATED ALWAYS AS IDENTITY is the modern way to create an auto-incrementing integer ID in PostgreSQL. You may also see SERIAL in older code, which is a shorthand that still works but is not the standard approach.

A table can have at most one primary key.

FOREIGN KEY

A foreign key ensures referential integrity: the value in one column must match an existing value in another table's column.

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT REFERENCES customers(id),
  total       NUMERIC
);

The database will reject any order whose customer_id does not exist in the customers table. You choose what happens when the referenced row is deleted:

Action	Behavior
ON DELETE RESTRICT	Prevents deleting the customer if orders exist (default)
ON DELETE CASCADE	Deletes orders automatically when the customer is deleted
ON DELETE SET NULL	Sets customer_id to NULL on the order
ON DELETE SET DEFAULT	Sets customer_id to its default value

Choose based on your domain's semantics. Cascading deletes are convenient but can surprise you: deleting one parent row can silently delete thousands of child rows.

Why constraints belong in the schema

Application code validates data at the point of entry. But database constraints enforce rules at the storage layer, regardless of how the data arrives. A migration script that bypasses the application, a developer running a quick fix in a SQL client, or a background job that takes a shortcut: all of these are governed by schema constraints. Application validation is helpful. Database constraints are authoritative.