Check constraints

Source URL: https://docs.prisma.io/docs/orm/more/troubleshooting/check-constraints

Check constraints

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.