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

概述

¥Overview

本页介绍如何在 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.

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

¥Check constraints can be added when you create the table (using CREATE TABLE) or to a table that already exists (using ALTER TABLE). This guide covers all four combinations.

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

¥At the end of the guide, you'll introspect your database, generate a Prisma Client, and write a simple Node.js script to validate the constraints.

先决条件

¥Prerequisites

为了遵循本指南，你需要：

¥In order to follow this guide, you need:

正在运行的 PostgreSQL 数据库服务器

¥a PostgreSQL database server running
createdb 命令行实用程序

¥the createdb command line utility
PostgreSQL 的 psql 命令行客户端

¥the psql command line client for PostgreSQL
你的机器上安装了 Node.js

¥Node.js installed on your machine

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

¥ Create a new database and project directory

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

¥Start by creating a project directory for the files that you'll create throughout this guide. Open terminal or command line and run the following commands:

mkdir check-demo
cd check-demo

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

¥Next, make sure that your PostgreSQL database server is running. Authenticate the default postgres user:

Unix（bash）：

sudo -u postgres

Windows（命令行）：

¥Windows (command line):

psql -U postgres

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

¥Then execute the following command in your terminal to create a new database called CheckDemo:

Unix（bash）：

createdb CheckDemo

Windows（命令行）：

¥Windows (command line):

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

提示：记住后面的 ;！postgres=# postgres-#

¥Tip: Remember the trailing ;! postgres=# postgres-#

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

¥You can validate that the database was created by running the \dt command which lists all tables (relations) in your database (right now there are none):

Unix（bash）：

psql -d CheckDemo -c "\dt"

Windows（命令行）：

¥Windows (command line):

-d CheckDemo -c \dt

2. 添加对单个列具有单个检查约束的表

¥ Adding a table with a single check constraint on a single column

在本部分中，你将创建一个新表，该表对 CheckDemo 数据库中的单个列具有单个检查约束。

¥In this section, you'll create a new table with a single check constraint on a single column in the CheckDemo database.

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

¥Create a new file named single-column-check-constraint.sql and add the following code to it:

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 的新表：

¥Now run the SQL statement against your database to create a new table called product:

Unix（bash）：

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

Windows（命令行）：

¥Windows (command line):

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

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

¥Congratulations, you just created a table called product in the database. The table has one column called price, which has a single check constraint that ensures price of a product is:

绝不小于 0.01

¥Never less than 0.01
永远不等于 1240.00

¥Never equal to 1240.00

运行以下命令查看适用于 product 表的检查约束列表：

¥Run the following command to see the a list of check constraints that apply to the product table:

\d+ product

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

¥You will see the following output, which includes a list of all check constraints:

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。

¥Note that PostgreSQL will auto-generate a constraint name if you do not provide one. For example, the constraint created by price NUMERIC CHECK (price > 0.01 AND price <> 1240.00) would be price_check.

3. 添加具有多列检查约束的表

¥ Adding a table with a multi-column check constraint

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

¥Next, you'll create a table with a multi-column check constraint that compares the values of two columns.

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

¥Create a new file named multi-column-check-constraint.sql and add the following code to it:

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 的新表：

¥Now run the SQL statement against your database to create a new table called anotherproduct:

Unix（bash）：

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

Windows（命令行）：

¥Windows (command line):

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

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

¥Congratulations, you just created a table called anotherproduct in the database. The table has two columns called reducedprice and price. The reducedprice column has a check constraint that ensures that the value of reducedprice is always less than the value of price.

4. 添加具有多个检查约束的表

¥ Adding a table with multiple check constraints

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

¥Next, you'll create a table with multiple check constraint on different columns.

创建一个名为 multiple-check-constraints.sql 的新文件并添加以下代码：

¥Create a new file named multiple-check-constraints.sql and add the following code to it:

  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 的新表：

¥Now run the SQL statement against your database to create a new table called secondtolastproduct:

Unix（bash）：

psql CheckDemo < multiple-check-constraints.sql

Windows（命令行）：

¥Windows (command line):

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

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

¥Congratulations, you just created a table called lastproduct in the database. The table has three columns named reducedprice, price and tags, and the following check constraints:

tags 列（这是一个数组）必须包含名为 product 的标记

¥The tags column (which is an array) must contain a tag named product
reducedprice 的值必须小于 price 的值

¥The value of reducedprice must be less than the value of price

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

¥ Adding a check constraint to an existing table

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

¥In this section, you'll add a check constraint to a table that already exists in your database. To do so, you first need to create a new table and then alter the table to add the constraint.

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

¥Create a new file named add-single-check-constraint-later.sql and add the following code:

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

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

这段代码包含两条 SQL 语句：

¥This code contains two SQL statements:

创建一个名为 lastproduct 的新表

¥Create a new table called lastproduct
更改表以添加名为 price_not_zero_constraint 的检查约束

¥Alter the table to add a check constraint named price_not_zero_constraint

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

¥Now run the SQL statements against your database to create a new table called lastproduct:

Unix（bash）：

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

Windows（命令行）：

¥Windows (command line):

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

恭喜，你刚刚在数据库中创建了一个名为 lastproduct 的表，其中包含一个名为 price 的列。你使用第二个 SQL 命令添加了名为 price_not_zero_constraint 的约束，这确保了产品的价格永远不会低于 0.01。

¥Congratulations, you just created a table called lastproduct in the database with a single column called price. You added constraint named price_not_zero_constraint to with a second SQL command, which ensures that the price of a product is never less than 0.01.

6. 使用 Prisma ORM 检查你的数据库

¥ Introspect your database with Prisma ORM

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

¥In the previous sections, you created four tables with different check constraints:

product 表具有检查约束，可确保 price 的值永远不会小于 0.01，并且永远不会恰好等于 1240.00。

¥The product table has a check constraint that ensures that the value of price is never less than 0.01 and never exactly 1240.00.
anotherproduct 表具有检查约束，可确保 reducedprice 的值永远不会大于 price 的值。

¥The anotherproduct table has a check constraint that ensures that the value of reducedprice is never greater than the value of price.
secondtolastproduct 表有两个检查约束 - 一种确保 reducedprice 的值永远不会大于 price 的值，另一种确保 tags 数组始终包含值 product。

¥The secondtolastproduct table has two check constraints - one that ensures that the value of reducedprice is never greater than the value of price, and one that ensures that the tags array always contains the value product.
lastproduct 表具有检查约束，可确保 category 的值永远不会是 clothing。

¥The lastproduct table has a check constraint that ensures that the value of category is never clothing.

在本节中，你将检查数据库以生成这些表的 Prisma 模型。

¥In this section you'll introspect your database to generate the Prisma models for these tables.

注意：检查约束当前不包含在生成的 Prisma 架构中 - 然而，底层数据库仍然强制执行这些约束。

¥Note: Check constraints are currently not included in the generated Prisma schema - however, the underlying database still enforces the constraints.

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

¥To start, set up a new Node.js project and add the prisma CLI as a development dependency:

npm init -y
npm install prisma --save-dev && npm install @prisma/client

为了内省你的数据库，你需要告诉 Prisma ORM 如何连接到它。你可以通过在 Prisma 架构中配置 datasource 来实现此目的。

¥In order to introspect your database, you need to tell Prisma ORM how to connect to it. You do so by configuring a datasource in your Prisma schema.

创建一个名为 schema.prisma 的新文件并添加以下代码：

¥Create a new file named schema.prisma and add the following code to it:

schema.prisma

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

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

¥The database connection URL is set via an environment variable. The Prisma CLI automatically supports the dotenv format which automatically picks up environment variables defined in a file named .env.

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

¥Create a new file named .env and set your database connection URL as the DATABASE_URL environment variable:

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

在上面的代码片段中，你需要将大写占位符替换为你自己的连接详细信息。例如，如果你的数据库在本地运行，它可能如下所示：

¥In the above code snippet, you need to replace the uppercase placeholders with your own connection details. For example, if your database is running locally it could look like this:

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

schema.prisma 和 .env 文件都就位后，你可以使用以下命令运行 Prisma ORM 的内省：

¥With both the schema.prisma and .env files in place, you can run Prisma ORM's introspection with the following command:

npx prisma db pull

此命令会检查你的数据库，并为每个表添加一个 Prisma 模型到 Prisma 架构中：

¥This command introspects your database and for each table adds a Prisma model to the Prisma schema:

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 客户端

¥ Generate Prisma Client

为了验证检查约束是否有效，你现在将生成 Prisma 客户端并向数据库发送一些示例查询。

¥To validate whether the check constraints work, you'll now generate Prisma Client and send a few sample queries to the database.

首先，将 generator 块添加到你的 Prisma 架构中（通常添加到 datasource 块的正下方）：

¥First, add a generator block to your Prisma schema (typically added right below the datasource block):

schema.prisma

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

运行以下命令在你的项目中安装并生成 Prisma 客户端：

¥Run the following command to install and generate Prisma Client in your project:

npx prisma generate

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

¥Now you can use Prisma Client to send database queries in Node.js.

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

¥ Validate the check constraints in a Node.js script

创建一个名为 index.js 的新文件并添加以下代码：

¥Create a new file named index.js and add the following code to it:

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 列配置的检查约束。

¥In this code, you're creating a product with a price of 0.00, which does not meet the check constraint configured for the price column.

使用以下命令运行代码：

¥Run the code with this command:

node index.js

该脚本抛出一个错误，指示不满足 price_check_value 检查约束：

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

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 中的代码替换为以下内容：

¥To validate the multi-column check constraint, replace the code in index.js with the following:

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

在此代码中，你创建的产品的折扣价高于实际价格。

¥In this code, you're creating a product where the reduced price is higher than the actual price.

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

¥Run the script again with this command:

node index.js

这次，你将看到类似的错误消息，指示未满足 reduce_price_check 检查约束：

¥This time, you'll see a similar error message indicating the reduce_price_check check constraint was not met:

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)

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

¥Finally, modify the script to include multiple check constraint violations:

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

在此代码中，你将创建一个 Markdown 高于实际价格的产品，并省略所需的 product 标签。

¥In this code, you're creating a product where the reduced price is higher than the actual price, and omitting the required product tag.

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

¥Run the script again with this command:

node index.js

请注意，错误消息仅提到 reduced_price_check 约束：

¥Notice that the error message only mentions the reduced_price_check constraint:

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

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

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

概述​

先决条件​

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

2. 添加对单个列具有单个检查约束的表​

3. 添加具有多列检查约束的表​

4. 添加具有多个检查约束的表​

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

6. 使用 Prisma ORM 检查你的数据库​

7. 生成 Prisma 客户端​

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

概述

先决条件

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

2. 添加对单个列具有单个检查约束的表

3. 添加具有多列检查约束的表

4. 添加具有多个检查约束的表

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

6. 使用 Prisma ORM 检查你的数据库

7. 生成 Prisma 客户端

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