PostgreSQL Constraint Patterns: Stop Bad Data at the Database Level

A payment status was set to refunded on a row where amount was still positive. A user’s email was changed to an empty string. A product was assigned to a category that did not exist. A booking was inserted with a start date after its end date. None of these should have been possible. All of them happened anyway.

The common thread: the application layer was the only line of defense. The Node.js service had validation middleware, Zod schemas, and business logic checks. But a race condition, a missed edge case, or a direct database connection from a migration script bypassed them all. The data hit disk, the downstream reporting pipeline consumed it, and someone had to write a corrective migration at 11 PM on a Friday.

Database constraints are the safety net that catches these failures at the point of write. They are the second layer of defense that does not depend on which application wrote to the database, which version of the code was deployed, or whether the validation library had a bug. This post covers five constraint patterns that eliminate entire classes of data corruption in PostgreSQL, with DDL you can copy, indexes you need to understand, and the error handling your Node.js code needs to surface them cleanly.

Pattern 1: CHECK constraints for business rules that must never break

Most developers know CHECK constraints exist. Most use them for trivial things like price > 0. But CHECK can enforce complex business rules that span multiple columns, and it is the right tool whenever a condition must be true for every row, every time, without exception.

Consider a subscription table:

CREATE TABLE subscriptions (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users(id),
  plan text NOT NULL,
  price_cents integer NOT NULL,
  trial_end timestamptz,
  started_at timestamptz NOT NULL DEFAULT now(),
  canceled_at timestamptz,
  CONSTRAINT positive_price CHECK (price_cents >= 0),
  CONSTRAINT trial_before_start CHECK (
    trial_end IS NULL OR trial_end > started_at
  ),
  CONSTRAINT cancel_after_start CHECK (
    canceled_at IS NULL OR canceled_at >= started_at
  ),
  CONSTRAINT trial_only_free CHECK (
    trial_end IS NULL OR price_cents = 0
  )
);

Five lines of CHECK constraints prevent:

• Negative prices (positive_price)
• Trial periods that end before the subscription starts (trial_before_start)
• Subscriptions canceled before they began (cancel_after_start)
• Paid subscriptions with a trial period (trial_only_free)

If a bug in your Node.js billing service sets price_cents = 0 for a non-trial plan, the trial_only_free constraint rejects the insert. The application catches a 23514 (check_violation) error and returns a 400 to the caller instead of silently persisting bad data.

This is especially valuable for columns that interact. A single-column CHECK is obvious. A multi-column CHECK is where you catch the expensive bugs.

Zero-downtime CHECK constraints with NOT VALID

Adding a CHECK constraint to an existing table that already has millions of rows can lock the table for a full sequential scan. PostgreSQL 9.2+ gives you a safer option:

ALTER TABLE subscriptions
  ADD CONSTRAINT trial_only_free
  CHECK (trial_end IS NULL OR price_cents = 0)
  NOT VALID;

-- The constraint applies to new writes immediately.
-- Validation happens in the background without blocking.
ALTER TABLE subscriptions VALIDATE CONSTRAINT trial_only_free;

NOT VALID skips the row scan during the ALTER TABLE. New inserts and updates are checked immediately. The VALIDATE CONSTRAINT step in the second command scans the table in a shareable lock mode that does not block concurrent reads or writes. If the validation finds existing rows that violate the constraint, it does not fail — it just reports them. You fix the data manually, then re-validate.

This pattern is how you add constraints to a live production table without a maintenance window.

Pattern 2: Partial unique indexes as conditional constraints

A UNIQUE constraint on (email) prevents duplicate emails. But what if you only care about uniqueness for active users? A deleted user with deleted_at IS NOT NULL should not block someone from registering the same email address.

A partial unique index solves this:

CREATE UNIQUE INDEX idx_active_user_email
  ON users (email)
  WHERE deleted_at IS NULL;

This index enforces email uniqueness only for non-deleted users. A new registration with email = 'alice@example.com' is rejected if an active user already has that email. If the only match is a deleted user, the insert succeeds.

The same pattern applies to soft-delete systems, multi-tenant soft deletes, and any scenario where uniqueness should skip certain rows:

-- Multi-tenant: one active slug per organization
CREATE UNIQUE INDEX idx_active_team_slug
  ON teams (org_id, slug)
  WHERE archived_at IS NULL;

-- Scheduled events: no overlapping time for the same resource
-- (works alongside EXCLUDE constraints for range types)
CREATE UNIQUE INDEX idx_active_event_slot
  ON events (resource_id, scheduled_at)
  WHERE status <> 'cancelled';

The advantage over a full UNIQUE constraint is that the index is smaller (fewer rows indexed) and the constraint is more precise (it only applies to the rows where the predicate matches). The tradeoff is that this is an index, not a constraint in the information schema, so some ORM tooling will not report it as a constraint. Your Node.js code still gets a 23505 (unique_violation) error on violation, which you handle the same way.

Pattern 3: Deferrable constraints for complex write workflows

Some business workflows require temporarily breaking a constraint within a transaction. A common example is a circular dependency between two tables: a user and their default project.

CREATE TABLE users (
  id bigserial PRIMARY KEY,
  default_project_id bigint
);

CREATE TABLE projects (
  id bigserial PRIMARY KEY,
  owner_id bigint NOT NULL REFERENCES users(id)
);

ALTER TABLE users
  ADD CONSTRAINT fk_default_project
  FOREIGN KEY (default_project_id) REFERENCES projects(id)
  DEFERRABLE INITIALLY DEFERRED;

With this setup, you can insert a user first (with default_project_id = NULL), insert the project, then update the user to set the default project. All inside a single transaction. The foreign key check is deferred until commit time:

import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function createUserWithDefaultProject(
  userName: string,
  projectName: string
): Promise<void> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    const userResult = await client.query(
      `INSERT INTO users (name) VALUES ($1) RETURNING id`,
      [userName]
    );
    const userId = userResult.rows[0].id;

    const projectResult = await client.query(
      `INSERT INTO projects (name, owner_id) VALUES ($1, $2) RETURNING id`,
      [projectName, userId]
    );
    const projectId = projectResult.rows[0].id;

    await client.query(
      `UPDATE users SET default_project_id = $1 WHERE id = $2`,
      [projectId, userId]
    );

    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Without DEFERRABLE, this insert sequence fails on the first INSERT INTO users because default_project_id references a project that does not exist yet. You would need to insert the user with NULL, then add the constraint, or reorder the operations in an unnatural way. DEFERRABLE INITIALLY DEFERRED lets you write the transaction in the logical order.

Use this sparingly. Deferred constraints add complexity and make constraint violation errors harder to reason about (they surface at COMMIT time, not INSERT time). Reserve them for the few cases where the application workflow genuinely requires a temporary violation, like circular references or batch import pipelines.

If you prefer INITIALLY IMMEDIATE but want to defer on demand:

ALTER TABLE users
  ADD CONSTRAINT fk_default_project
  FOREIGN KEY (default_project_id) REFERENCES projects(id)
  DEFERRABLE INITIALLY IMMEDIATE;

-- Inside a transaction, defer it:
SET CONSTRAINTS fk_default_project DEFERRED;

This gives you the best of both worlds: the constraint is enforced immediately by default, but you can opt into deferral for specific operations.

Pattern 4: Identity columns instead of manual sequence management

PostgreSQL 10 introduced GENERATED AS IDENTITY, which replaces the old SERIAL pseudotype. The difference matters for data integrity:

-- Old way (still works, but less safe):
CREATE TABLE orders_old (
  id bigserial PRIMARY KEY
);

-- Better way (PostgreSQL 10+):
CREATE TABLE orders (
  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  customer_id bigint NOT NULL,
  total_cents integer NOT NULL CHECK (total_cents >= 0)
);

GENERATED ALWAYS AS IDENTITY prevents direct insert or update of the identity column. If someone writes a raw SQL statement or a migration script that explicitly sets the ID, the database rejects it:

INSERT INTO orders (id, customer_id, total_cents) VALUES (1, 42, 1500);
-- ERROR: cannot insert a non-DEFAULT value into column "id"

-- This works:
INSERT INTO orders (customer_id, total_cents) VALUES (42, 1500);
-- id is auto-generated

The GENERATED BY DEFAULT variant allows explicit inserts but warns about conflicts. Use ALWAYS for stricter integrity.

There is another benefit: identity columns track the sequence as an attribute of the column, not as a separate database object. When you delete a table with a SERIAL column, the sequence lingers unless you DROP SEQUENCE explicitly. Identity columns clean up automatically.

For seed scripts and test factories, you can override the identity constraint by inserting OVERRIDING SYSTEM VALUE:

INSERT INTO orders (id, customer_id, total_cents)
  OVERRIDING SYSTEM VALUE
  VALUES (100, 42, 1500);

This is explicit. You cannot accidentally insert a hardcoded ID; you must declare the intent.

Pattern 5: NOT NULL with a purpose, and CHECK for conditional nullability

NOT NULL is the simplest constraint. But most schemas use it as a default reflex instead of a deliberate choice. Every NOT NULL column should come with a question: “Is there any state transition in this system where this column could legitimately be null?”

If the answer is yes, do not make the column nullable. Use a partial constraint instead:

CREATE TABLE orders (
  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  status text NOT NULL CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
  shipped_at timestamptz,
  delivered_at timestamptz,
  CONSTRAINT shipped_requires_confirmed CHECK (
    (shipped_at IS NULL AND status NOT IN ('shipped', 'delivered'))
    OR (shipped_at IS NOT NULL AND status IN ('shipped', 'delivered'))
  ),
  CONSTRAINT delivered_requires_shipped CHECK (
    (delivered_at IS NULL AND status <> 'delivered')
    OR (delivered_at IS NOT NULL AND status = 'delivered')
  )
);

These constraints encode the state machine directly in the schema:

• shipped_at can only be set when status is shipped or delivered
• delivered_at can only be set when status is delivered
• If status is shipped, shipped_at must be set

Your application logic still manages the transitions. But if a bug, a manual SQL query, or a migration script tries to set delivered_at on a pending order, the constraint rejects it.

Handling constraint violations in Node.js

PostgreSQL returns specific error codes for each constraint type. Your Node.js error handler should map these to meaningful HTTP responses:

import { DatabaseError } from 'pg';
import type { Request, Response, NextFunction } from 'express';

interface ConstraintHandler {
  code: string;
  status: number;
  message: (err: DatabaseError) => string;
}

const CONSTRAINT_HANDLERS: Record<string, ConstraintHandler> = {
  '23503': { // foreign_key_violation
    code: '23503',
    status: 409,
    message: (err) =>
      `Referenced record does not exist: ${err.constraint}`,
  },
  '23505': { // unique_violation
    code: '23505',
    status: 409,
    message: (err) =>
      `Duplicate value violates unique constraint: ${err.constraint}`,
  },
  '23514': { // check_violation
    code: '23514',
    status: 422,
    message: (err) =>
      `Business rule violation: ${err.constraint}`,
  },
};

function postgresErrorHandler(
  err: Error,
  _req: Request,
  res: Response,
  _next: NextFunction
): void {
  const dbErr = err as DatabaseError;
  const handler = CONSTRAINT_HANDLERS[dbErr.code];

  if (handler) {
    res.status(handler.status).json({
      error: handler.message(dbErr),
      constraint: dbErr.constraint,
    });
    return;
  }

  res.status(500).json({ error: 'Internal server error' });
}

This handler returns:

• 409 Conflict for foreign key and unique violations (the client can retry with different data)
• 422 Unprocessable Entity for CHECK violations (the request data contains an invalid business state)
• 500 for everything else

The constraint field in the response lets the frontend or API consumer know exactly which rule was violated, which is far more useful than a generic “validation error” message.

For a full-featured implementation, add logging and structured error reporting:

function postgresErrorHandler(
  err: Error,
  _req: Request,
  res: Response,
  next: NextFunction
): void {
  const dbErr = err as DatabaseError;
  const handler = CONSTRAINT_HANDLERS[dbErr.code];

  if (handler) {
    logger.warn({
      constraint: dbErr.constraint,
      code: dbErr.code,
      detail: dbErr.detail || dbErr.message,
      query: dbErr.query?.substring(0, 200),
    }, 'Database constraint violation');

    res.status(handler.status).json({
      error: handler.message(dbErr),
      constraint: dbErr.constraint,
    });
    return;
  }

  if (dbErr.code && dbErr.code.startsWith('23')) {
    logger.error({ err: dbErr }, 'Unhandled integrity constraint violation');
  }

  next(err);
}

Putting it all together: a migration that adds constraints to an existing table

Here is a production-safe migration script that adds multiple constraint patterns to a legacy table without downtime:

-- Step 1: Add CHECK constraints with NOT VALID
ALTER TABLE orders
  ADD CONSTRAINT positive_total
  CHECK (total_cents >= 0)
  NOT VALID;

ALTER TABLE orders
  ADD CONSTRAINT valid_status
  CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled'))
  NOT VALID;

-- Step 2: Validate existing data (non-blocking)
ALTER TABLE orders VALIDATE CONSTRAINT positive_total;
ALTER TABLE orders VALIDATE CONSTRAINT valid_status;

-- Step 3: Add partial unique index for active orders
CREATE UNIQUE INDEX CONCURRENTLY idx_active_order_per_customer
  ON orders (customer_id)
  WHERE status <> 'cancelled';

-- Step 4: Add foreign key with NOT VALID for zero-downtime
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;

Each step uses either CONCURRENTLY or NOT VALID to avoid blocking writes. The entire migration can run while the application is serving traffic.

When to skip constraints

Constraints are not free. Every CHECK and FOREIGN KEY adds a validation step on every insert and update. For write-heavy tables processing millions of rows per minute, the overhead of multiple constraint checks can add up.

Benchmark your write path with and without constraints to know your numbers. In one production system I worked on, adding seven CHECK constraints to a 2,000-row-per-second insert pipeline added 3% to the p99 write latency. The cost was negligible. The benefit was catching three data quality bugs in the first month.

If you truly cannot afford the overhead, validate constraints in batches instead of per-row:

-- Instead of per-row CHECK constraints, use a nightly or post-batch validation:
CREATE TABLE constraint_violations AS
SELECT id, 'positive_total' AS constraint_name
FROM orders
WHERE total_cents < 0;

But this is a last resort. By the time you find the violation, downstream systems have already consumed the bad data. The constraint prevents the violation at write time. The batch check only reports it.

The takeaway

Your application validation library is not enough. It runs in one process on one version of your code. Direct database connections, migration scripts, data imports, and race conditions all bypass it. Database constraints are the last line of defense, and they work regardless of which client wrote the data or what version of the application was deployed.

Start small. Pick one table that has caused a data quality incident in the last six months. Add a CHECK constraint for the rule that was violated. Use NOT VALID so you can deploy without downtime. Then add the Node.js error handler so the violation surfaces as a clean HTTP response instead of a 500. Once you see how much cleaner your data is, you will know exactly which table to fix next.

The patterns in this post cover the most common integrity gaps I see in production PostgreSQL schemas. None of them require new infrastructure, new tooling, or a schema redesign. They are DDL statements you can run this afternoon, and they start paying for themselves the first time they catch a bug your test suite missed.

A note from Yojji

Database-level data integrity is one of those engineering disciplines that separates teams who trust their data from teams who constantly fight data quality fires. Building robust constraint patterns into the schema from the start means every application, every migration, and every ad-hoc query is held to the same standard. Yojji’s engineering teams treat database constraints as a core part of the architecture, not an afterthought. With deep experience in PostgreSQL and the Node.js ecosystem, they help teams design data models that stay clean as they scale, without relying on application code to catch every edge case.

Yojji is an international custom software development company with offices in Europe, the US, and the UK, specializing in full-cycle product development and dedicated team augmentation.