Data validation with CHECK constraints (PostgreSQL)

Learn how to configure CHECK constraints for data validation with Prisma ORM and PostgreSQL

This page explains how to configure check constraints in a PostgreSQL database. A check constraint is a condition that must be satisfied before a value can be saved to a table - for example, the discounted price of a product must always be less than the original price.

Check constraints can be added when you create the table (using CREATE TABLE) or to a table that already exists (using ALTER TABLE).

Prerequisites

A PostgreSQL database server running
The psql command line client
Node.js installed

Single column check constraint

Create a table with a check constraint on a single column:

CREATE TABLE "public"."product" (
  price NUMERIC CONSTRAINT price_value_check CHECK (price > 0.01 AND price <> 1240.00)
);
ALTER TABLE "public"."product"
  ADD COLUMN "productid" serial,
  ADD PRIMARY KEY ("productid");

This ensures the price is never less than 0.01 and never equal to 1240.00.

Multi-column check constraint

Create a table with a check constraint that compares values of two columns:

CREATE TABLE "public"."anotherproduct" (
  reducedprice NUMERIC CONSTRAINT reduced_price_check CHECK (price > reducedprice),
  price NUMERIC
);
ALTER TABLE "public"."anotherproduct"
  ADD COLUMN "productid" serial,
  ADD PRIMARY KEY ("productid");

This ensures reducedprice is always less than price.

Multiple check constraints

CREATE TABLE "public"."secondtolastproduct" (
  reducedprice NUMERIC CONSTRAINT reduced_price_check CHECK (price > reducedprice),
  price NUMERIC,
  tags TEXT[] CONSTRAINT tags_contains_product CHECK ('product' = ANY(tags))
);
ALTER TABLE "public"."secondtolastproduct"
  ADD COLUMN "productid" serial,
  ADD PRIMARY KEY ("productid");

Adding check constraints to existing tables

CREATE TABLE "public"."lastproduct" (
  category TEXT
);

ALTER TABLE "public"."lastproduct"
  ADD CONSTRAINT "category_not_clothing" CHECK (category <> 'clothing');

Using with Prisma ORM

After introspection, your Prisma schema will include the models but the check constraints are enforced at the database level:

model product {
  price     Float?
  productid Int    @id
}

model anotherproduct {
  price        Float?
  productid    Int    @id
  reducedprice Float?
}

Generate Prisma Client and test:

const { PrismaClient } = require("../prisma/generated/client");

const prisma = new PrismaClient();

async function main() {
  // This will fail due to the check constraint
  const newProduct = await prisma.product.create({
    data: {
      price: 0.0, // violates price > 0.01
    },
  });
}

main();

The script throws an error indicating the price_check_value check constraint was not met:

Error: new row for relation "product" violates check constraint "price_value_check"

Check constraints are resolved in alphabetical order, and only the first constraint to fail appears in the error message.

Check constraints