跳到主要內容

使用 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 語句

  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 約束按字母順序解析，並且只有第一個失敗的約束會出現在錯誤訊息中。

© . This site is unofficial and not affiliated with Prisma Data, Inc.