跳到主要内容

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

概述

本页解释了如何在 PostgreSQL 数据库中配置CHECK 约束。CHECK 约束是在值保存到表之前必须满足的条件——例如,产品的折扣价必须始终低于原始价格。

CHECK 约束可以在创建表时(使用 CREATE TABLE)或添加到已存在的表(使用 ALTER TABLE)中。本指南涵盖所有四种组合。

在本指南的最后,你将内省你的数据库,生成 Prisma Client,并编写一个简单的 Node.js 脚本来验证这些约束。

先决条件

为了遵循本指南,你需要

  • 一个正在运行的 PostgreSQL 数据库服务器
  • createdb 命令行工具
  • 用于 PostgreSQL 的 psql 命令行客户端
  • 你的机器上已安装 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. 添加一个在单列上带有一个 CHECK 约束的表

在本节中,你将在 CheckDemo 数据库中创建一个在单列上带有一个 CHECK 约束的新表

创建一个名为 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 的列,该列带有一个 CHECK 约束,确保产品的价格是

  • 从不小于 0.01
  • 从不等于 1240.00

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

\d+ product

你将看到以下输出,其中包含所有 CHECK 约束的列表

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. 添加一个带有多列 CHECK 约束的表

接下来,你将创建一个带有多列 CHECK 约束的表,该约束比较两列的值。

创建一个名为 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 的表。该表有两个列,名为 reducedpricepricereducedprice 列带有一个 CHECK 约束,确保 reducedprice 的值始终小于 price 的值。

4. 添加一个带有多个 CHECK 约束的表

接下来,你将创建一个在不同列上带有多个 CHECK 约束的表

创建一个名为 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 的表。该表有三个列,名为 reducedpricepricetags,以及以下 CHECK 约束

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

5. 向现有表添加 CHECK 约束

在本节中,你将向数据库中已存在的表添加一个 CHECK 约束。为此,你首先需要创建一个新表,然后修改该表以添加约束。

创建一个名为 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 的 CHECK 约束

现在,针对你的数据库运行 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 内省你的数据库

在前面的章节中,你创建了四个带有不同 CHECK 约束的表

  • product 表带有一个 CHECK 约束,确保 price 的值从不小于 0.01 且从不精确等于 1240.00
  • anotherproduct 表带有一个 CHECK 约束,确保 reducedprice 的值从不大于 price 的值。
  • secondtolastproduct 表有两个 CHECK 约束——一个确保 reducedprice 的值从不大于 price 的值,另一个确保 tags 数组始终包含值 product
  • lastproduct 表带有一个 CHECK 约束,确保 category 的值从不为 clothing

在本节中,你将内省你的数据库,为这些表生成 Prisma 模型。

注意:CHECK 约束目前未包含在生成的 Prisma schema 中——然而,底层数据库仍然强制执行这些约束。

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

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

为了内省你的数据库,你需要告诉 Prisma ORM 如何连接到它。你可以通过在 Prisma schema 中配置一个 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 schema 添加一个 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

为了验证 CHECK 约束是否有效,你现在将生成 Prisma Client 并向数据库发送一些示例查询。

首先,将 generator 块添加到你的 Prisma schema 中(通常添加在 datasource 块的正下方)

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

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

npx prisma generate

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

8. 在 Node.js 脚本中验证 CHECK 约束

创建一个名为 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 列配置的 CHECK 约束。

使用此命令运行代码

node index.js

脚本抛出一个错误,指示 price_check_value CHECK 约束未满足

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

要验证多列 CHECK 约束,请将 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 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)

最后,修改脚本以包含多个 CHECK 约束违规

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

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

© . All rights reserved.