跳至主要内容

使用 CHECK 约束进行数据验证 (PostgreSQL)

概述​

本页说明如何在 PostgreSQL 数据库中配置 检查约束。检查约束是在将值保存到表之前必须满足的条件 - 例如，产品的折扣价必须始终低于原始价格。

在创建表（使用 CREATE TABLE）或向已存在的表（使用 ALTER TABLE）添加时，都可以添加检查约束。本指南涵盖所有四种组合。

在指南的最后，您将内省您的数据库，生成 Prisma 客户端，并编写一个简单的 Node.js 脚本来验证约束。

先决条件​

为了遵循本指南，您需要

  • 一个 PostgreSQL 数据库服务器正在运行
  • the createdb 命令行实用程序
  • the psql 用于 PostgreSQL 的命令行客户端
  • Node.js 安装在您的机器上

1. 创建一个新的数据库和项目目录​

首先为将在本指南中创建的文件创建一个项目目录。打开终端或命令行并运行以下命令

mkdir check-demo
cd check-demo

接下来，确保您的 PostgreSQL 数据库服务器正在运行。验证默认的 postgres 用户

Unix (bash)

sudo -u postgres

Windows (命令行)

psql -U postgres

然后在您的终端中执行以下命令，创建一个名为 CheckDemo 的新数据库

Unix (bash)

createdb CheckDemo

Windows (命令行)

create database CheckDemo;
//delete-next-line
\connect CheckDemo

提示: 请记住最后的 ;！ postgres=# postgres-#

您可以通过运行 \dt 命令来验证数据库是否已创建，该命令将列出数据库中的所有表（关系）（目前还没有表）。

Unix (bash)

psql -d CheckDemo -c "\dt"

Windows (命令行)

-d CheckDemo -c \dt

2. 向表添加一个单列上的单一检查约束​

在本节中，您将在 CheckDemo 数据库中创建一个具有单列上的单一检查约束的新表。

创建一个名为 single-column-check-constraint.sql 的新文件，并将以下代码添加到其中

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

现在将 SQL 语句对您的数据库运行，以创建一个名为 product 的新表

Unix (bash)

psql CheckDemo < single-column-check-constraint.sql

Windows (命令行)

\i 'c:/checkdemo/single-column-check-constraint.sql'

恭喜您，您刚刚在数据库中创建了一个名为 product 的表。该表有一列名为 price，它有一个单一检查约束，确保产品的价格

  • 绝不低于 0.01
  • 绝不等于 1240.00

运行以下命令以查看应用于 product 表的所有检查约束的列表

\d+ product

您将看到以下输出，其中包含所有检查约束的列表

Table "public.product"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 price  | numeric |           |          |         | main    |              |
Check constraints:
    "price_value_check" CHECK (price > 0.01 AND price <> 1240.00)

请注意，如果未提供约束名称，PostgreSQL 将自动生成一个约束名称。例如，由 price NUMERIC CHECK (price > 0.01 AND price <> 1240.00) 创建的约束将为 price_check。

3. 向表添加一个多列检查约束​

接下来，您将创建一个具有比较两个列的值的多列检查约束的表。

创建一个名为 multi-column-check-constraint.sql 的新文件，并将以下代码添加到其中

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

现在将 SQL 语句对您的数据库运行，以创建一个名为 anotherproduct 的新表

Unix (bash)

psql CheckDemo < multi-column-check-constraint.sql

Windows (命令行)

\i 'c:/checkdemo/multi-column-check-constraint.sql'

恭喜您，您刚刚在数据库中创建了一个名为 anotherproduct 的表。该表有两列名为 reducedprice 和 price。reducedprice 列有一个检查约束，确保 reducedprice 的值始终小于 price 的值。

4. 向表添加多个检查约束​

接下来，您将在不同的列上创建一个具有多个检查约束的表。

创建一个名为 multiple-check-constraints.sql 的新文件，并将以下代码添加到其中

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

现在将 SQL 语句对您的数据库运行，以创建一个名为 secondtolastproduct 的新表

Unix (bash)

psql CheckDemo < multiple-check-constraints.sql

Windows (命令行)

\i 'c:/checkdemo/multiple-check-constraints.sql'

恭喜您，您刚刚在数据库中创建了一个名为 lastproduct 的表。该表有三个列名为 reducedprice、price 和 tags，并且具有以下检查约束

  • tags 列（是一个数组）必须包含一个名为 product 的标签
  • reducedprice 的值必须小于 price 的值

5. 向现有表添加检查约束​

在本节中，您将向数据库中已存在的表添加检查约束。为此，您首先需要创建一个新表，然后更改该表以添加约束。

创建一个名为 add-single-check-constraint-later.sql 的新文件，并将以下代码添加到其中

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

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

此代码包含两个 SQL 语句

  1. 创建一个名为 lastproduct 的新表
  2. 更改表以添加一个名为 price_not_zero_constraint 的检查约束

现在将 SQL 语句对您的数据库运行，以创建一个名为 lastproduct 的新表

Unix (bash)

psql CheckDemo < add-single-check-constraint-later.sql

Windows (命令行)

\i 'c:/checkdemo/add-single-check-constraint-later.sql'

恭喜您，您刚刚在数据库中创建了一个名为 lastproduct 的表，它只有一列名为 price。您使用第二个 SQL 命令添加了一个名为 price_not_zero_constraint 的约束，它确保产品的价格绝不低于 0.01。

6. 使用 Prisma ORM 内省您的数据库​

在前面的部分中，您创建了四个具有不同检查约束的表

  • product 表有一个检查约束，确保 price 的值绝不低于 0.01，且绝不完全等于 1240.00。
  • anotherproduct 表有一个检查约束，确保 reducedprice 的值绝不高于 price 的值。
  • secondtolastproduct 表有两个检查约束 - 一个确保 reducedprice 的值绝不高于 price 的值，另一个确保 tags 数组始终包含值 product。
  • lastproduct 表有一个检查约束，确保 category 的值绝不为 clothing。

在本节中，您将内省您的数据库以生成这些表的 Prisma 模型。

注意：生成的 Prisma 模式目前不包含检查约束，但底层数据库仍然会强制执行这些约束。

首先，设置一个新的 Node.js 项目并将 prisma CLI 添加为开发依赖项。

npm init -y
npm install prisma --save-dev

为了内省您的数据库，您需要告诉 Prisma ORM 如何连接到它。您可以在 Prisma 模式中配置一个 datasource 来实现这一点。

创建一个名为 schema.prisma 的新文件，并将以下代码添加到其中。

schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

数据库连接 URL 通过环境变量设置。Prisma CLI 自动支持 dotenv 格式，该格式会自动获取在名为 .env 的文件中定义的环境变量。

创建一个名为 .env 的新文件，并将您的数据库连接 URL 作为 DATABASE_URL 环境变量设置。

DATABASE_URL=postgresql://__USER__:__PASSWORD__@__HOST__:__PORT__/CheckDemo

在上面的代码片段中，您需要将大写占位符替换为自己的连接详细信息。例如，如果您的数据库在本地运行，它可能看起来像这样。

DATABASE_URL=postgresql://janedoe:mypassword@localhost:5432/CheckDemo

将 schema.prisma 和 .env 文件都准备好后，您可以使用以下命令运行 Prisma ORM 的内省功能。

npx prisma db pull

此命令会内省您的数据库，并为每个表在 Prisma 模式中添加一个 Prisma 模型。

schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

model lastproduct {
  category  String?
  productid Int     @id
}

model product {
  price     Float?
  productid Int    @id
}

model secondtolastproduct {
  price        Float?
  productid    Int      @id
  reducedprice Float?
  tags         String[]
}

7. 生成 Prisma Client​

为了验证检查约束是否有效，您现在需要生成 Prisma Client 并向数据库发送一些示例查询。

首先，在您的 Prisma 模式中添加一个 generator 块（通常添加在 datasource 块的正下方）。

schema.prisma
generator client {
  provider = "prisma-client-js"
}

运行以下命令以在您的项目中安装和生成 Prisma Client。

npx prisma generate

现在，您可以使用 Prisma Client 在 Node.js 中发送数据库查询。

8. 在 Node.js 脚本中验证检查约束​

创建一个名为 index.js 的新文件，并将以下代码添加到其中。

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  const newProduct = await prisma.product.create({
    data: {
      price: 0.0,
    },
  })

  console.log(newProduct)
}

main()

在此代码中，您将创建一个价格为 0.00 的产品，这并不满足为 price 列配置的检查约束。

使用以下命令运行代码。

node index.js

该脚本会抛出一个错误，表明 price_check_value 检查约束未满足。

Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("23514"), message: "new row for relation \"product\" violates check constraint \"price_value_check\"", detail: Some("Failing row contains (0, 11)."), hint: None, position: None, where_: None, schema: Some("public"), table: Some("product"), column: None, datatype: None, constraint: Some("price_value_check"), file: Some("d:\\pginstaller_12.auto\\postgres.windows-x64\\src\\backend\\executor\\execmain.c"), line: Some(2023), routine: Some("ExecConstraints") }) }) })

为了验证多列检查约束，请将 index.js 中的代码替换为以下代码。

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  const newProduct = await prisma.anotherproduct.create({
    data: {
      price: 50.0,
      reducedprice: 100.0,
    },
  })

  console.log(newProduct)
}

main()

在此代码中，您将创建一个产品，其中优惠价格高于实际价格。

再次使用以下命令运行该脚本。

node index.js

这次，您将看到一条类似的错误消息，表明 reduce_price_check 检查约束未满足。

ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("23514"), message: "new row for relation \"anotherproduct\" violates check constraint \"reduced_price_check\"", detail: Some("Failing row contains (100, 50, 1)."), hint: None, position: None, where_: None, schema: Some("public"), table: Some("anotherproduct"), column: None, datatype: None, constraint: Some("reduced_price_check"), file: Some("d:\\pginstaller_12.auto\\postgres.windows-x64\\src\\backend\\executor\\execmain.c"), line: Some(2023), routine: Some("ExecConstraints") }) }) })
    at PrismaClientFetcher.request (C:\Work\Personal\prisma-check-constraint\node_modules\@prisma\client\index.js:89:17)

最后，修改该脚本以包含多个检查约束违规。

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  const newProduct = await prisma.secondtolastproduct.create({
    data: {
      tags: {
        set: ['wrongtag'],
      },
      price: 90.0,
      reducedprice: 100.0,
    },
  })

  console.log(newProduct)
}

main()

在此代码中，您将创建一个产品，其中优惠价格高于实际价格，并且省略了必需的 product 标签。

再次使用以下命令运行该脚本。

node index.js

注意，错误消息只提到了 reduced_price_check 约束。

ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("23514"), message: "new row for relation \"secondtolastproduct\" violates check constraint \"reduced_price_check\"", detail: Some("Failing row contains (100, 90, {wrongtag}, 7)."), hint: None, position: None, where_: None, schema: Some("public"), table: Some("secondtolastproduct"), column: None, datatype: None, constraint: Some("reduced_price_check"), file: Some("d:\\pginstaller_12.auto\\postgres.windows-x64\\src\\backend\\executor\\execmain.c"), line: Some(2023), routine: Some("ExecConstraints") }) }) })

检查约束按字母顺序解析，并且只有第一个失败的约束会出现在错误消息中。