使用 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
为了内省您的数据库,您需要告诉 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 并向数据库发送一些示例查询。
首先,向您的 Prisma schema 添加一个 generator
块(通常添加到 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 约束按字母顺序解析,只有第一个失败的约束出现在错误消息中。