跳到主要內容

使用 CHECK 檢查約束進行資料驗證 (PostgreSQL)

總覽​

本頁面說明如何在 PostgreSQL 資料庫中配置檢查約束。檢查約束是一種條件，必須在將值儲存到資料表之前滿足 - 例如，產品的折扣價格必須始終低於原始價格。

檢查約束可以在您建立資料表時（使用 CREATE TABLE）或在已存在的資料表上新增（使用 ALTER TABLE）。本指南涵蓋所有四種組合。

在本指南的最後，您將內省您的資料庫，產生 Prisma Client，並編寫一個簡單的 Node.js 腳本來驗證約束。

先決條件​

為了遵循本指南，您需要

  • 一個正在運行的 PostgreSQL 資料庫伺服器
  • createdb 命令列工具
  • psql PostgreSQL 的命令列用戶端
  • 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. 新增具有單一欄位檢查約束的資料表​

在本節中，您將在 CheckDemo 資料庫中建立一個新的資料表，其中在單一欄位上具有單一檢查約束。

建立一個名為 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 的欄位，該欄位具有單一檢查約束，可確保產品的價格為

  • 永遠不低於 0.01
  • 永遠不等於 1240.00

運行以下命令以查看適用於 product 資料表的檢查約束列表

\d+ product

您將看到以下輸出，其中包含所有檢查約束的列表

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. 新增具有多欄位檢查約束的資料表​

接下來，您將建立一個具有多欄位檢查約束的資料表，該約束比較兩個欄位的值。

建立一個名為 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 欄位具有檢查約束，可確保 reducedprice 的值始終小於 price 的值。

4. 新增具有多個檢查約束的資料表​

接下來，您將在不同欄位上建立一個具有多個檢查約束的資料表。

建立一個名為 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 的欄位，以及以下檢查約束

  • tags 欄位（它是一個陣列）必須包含一個名為 product 的標籤
  • reducedprice 的值必須小於 price 的值

5. 將檢查約束新增到現有資料表​

在本節中，您將將檢查約束新增到資料庫中已存在的資料表。為此，您首先需要建立一個新的資料表，然後變更資料表以新增約束。

建立一個名為 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 的檢查約束

現在針對您的資料庫運行 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 內省您的資料庫​

在之前的章節中，您建立了四個具有不同檢查約束的資料表

  • product 資料表具有檢查約束，可確保 price 的值永遠不低於 0.01 且永遠不完全等於 1240.00。
  • anotherproduct 資料表具有檢查約束，可確保 reducedprice 的值永遠不大於 price 的值。
  • secondtolastproduct 資料表具有兩個檢查約束 - 一個確保 reducedprice 的值永遠不大於 price 的值，另一個確保 tags 陣列始終包含值 product。
  • lastproduct 資料表具有檢查約束，可確保 category 的值永遠不是 clothing。

在本節中，您將內省您的資料庫，以產生這些資料表的 Prisma 模型。

注意：檢查約束目前未包含在產生的 Prisma schema 中 - 但是，底層資料庫仍然會強制執行約束。

首先，設定一個新的 Node.js 專案，並將 prisma CLI 作為開發相依性新增

npm init -y
npm install prisma --save-dev

為了內省您的資料庫，您需要告訴 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 模型新增到 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 Client​

為了驗證檢查約束是否有效，您現在將產生 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 腳本中驗證檢查約束​

建立一個名為 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 欄位配置的檢查約束。

使用此命令運行程式碼

node index.js

該腳本拋出一個錯誤，指示未滿足 price_check_value 檢查約束

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 中的程式碼替換為以下內容

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 檢查約束

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)

最後，修改腳本以包含多個檢查約束違規

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

檢查約束按字母順序解析，並且僅在錯誤訊息中顯示第一個失敗的約束。