使用 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
的表。该表有两个列,名为 reducedprice
和 price
。reducedprice
列带有一个 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
的表。该表有三个列,名为 reducedprice
、price
和 tags
,以及以下 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 语句
- 创建一个名为
lastproduct
的新表 - 修改表以添加名为
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
的新文件,并将以下代码添加到其中
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 模型
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
块的正下方)
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 约束按字母顺序解析,并且只有第一个失败的约束会出现在错误消息中。