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

Start here. Go through every column you are defining and ask: can this ever be unknown? If the answer is no, add NOT NULL. Columns are nullable by default, which means NULL will show up if you ever forget to supply a value.

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

Be deliberate about which columns you leave nullable. Every nullable column is a place where NULL will eventually show up and need to be handled.

UNIQUE

A UNIQUE constraint means the database will reject any insert or update that would create a duplicate in that column:

email TEXT NOT NULL UNIQUE

You can also constrain combinations of columns:

UNIQUE (user_id, product_id)

Each user_id can appear in many rows. Each product_id can appear in many rows. What cannot happen is the same pair appearing twice. This is exactly right for a favorites or bookmarks table - a user can save many products, but cannot 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'))
);

On every INSERT or UPDATE, the database evaluates the expression. If it comes back false, the write is rejected. This is the right place to encode business rules that should never be violated - negative prices, invalid status values, end dates before start dates.

Naming your constraints makes the error messages much easier to read in logs and application error handling:

CONSTRAINT price_non_negative CHECK (price >= 0)

PRIMARY KEY

Every table needs a primary key - it is how you uniquely identify each row. Adding PRIMARY KEY implicitly applies both NOT NULL and UNIQUE, so you do not need to declare those separately.

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

GENERATED ALWAYS AS IDENTITY is the current standard for auto-incrementing IDs in PostgreSQL. You will see SERIAL in older codebases - it still works, but it is a legacy shorthand. Use the identity syntax for new tables.

A table can have at most one primary key.

FOREIGN KEY

Foreign keys are how you tell the database that a relationship between tables must stay consistent. Without one, nothing stops you from inserting an order that points to a customer ID that does not exist.

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

The database will reject any insert where customer_id does not exist in customers. You also control what happens when the referenced customer row is deleted:

Choose based on what makes sense for your data model. Cascade is convenient but worth being careful with - deleting one customer can silently take thousands of orders with it.

The real reason constraints matter

Application code validates data at the entry point - a form submission, an API call. That covers the normal path. But data does not always flow through the application. A migration script, a developer running a quick fix in a SQL client, a background job that skips validation: none of these go through your application layer. Schema constraints catch all of them. Put the rules in the schema, and the database enforces them regardless of where the write comes from.