Check constraints (Prisma ORM v6) (/docs/orm/v6/more/troubleshooting/check-constraints)

For the complete Prisma documentation index, see llms.txt. A markdown version of any docs page is available by appending .md to its URL.

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

Location: ORM > v6 > More > Troubleshooting > Check constraints

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).

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

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.

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.

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");

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

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

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.

• Bundler issues: Solve ENOENT package error with vercel/pkg and other bundlers
• GraphQL autocompletion: Get autocompletion for Prisma Client queries in GraphQL resolvers with plain JavaScript
• Many-to-many relations: Learn how to model, query, and convert many-to-many relations with Prisma ORM
• Next.js: Best practices and troubleshooting for using Prisma ORM with Next.js applications.
• Nuxt: Learn how to integrate Prisma ORM with your Nuxt application.