CHECK制約によるデータ検証 (PostgreSQL)

概要

このページでは、PostgreSQLデータベースでチェック制約を設定する方法について説明します。チェック制約は、値をテーブルに保存する前に満たす必要のある条件です。たとえば、製品の割引価格は常に元の価格よりも低くなければなりません。

チェック制約は、テーブルを作成するとき（CREATE TABLEを使用）または既存のテーブル（ALTER TABLEを使用）に追加できます。このガイドでは、4つの組み合わせすべてを網羅します。

ガイドの最後には、データベースをイントロスペクションし、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という名前のカラムが1つあり、製品の価格が次のようになることを保証する単一のチェック制約があります。

• 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. 複数カラムのチェック制約を持つテーブルを追加する

次に、2つのカラムの値を比較する複数カラムのチェック制約を持つテーブルを作成します。

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という名前の2つのカラムがあります。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という名前の3つのカラムがあり、次のチェック制約があります。

• 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');

このコードには、2つの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'

おめでとうございます。priceという名前の単一のカラムを持つlastproductという名前のテーブルをデータベースに作成しました。2番目のSQLコマンドでprice_not_zero_constraintという名前の制約を追加しました。これにより、製品の価格が0.01未満になることはありません。

6. Prisma ORMでデータベースをイントロスペクションする

前のセクションでは、異なるチェック制約を持つ4つのテーブルを作成しました。

• productテーブルには、priceの値が0.01未満になることはなく、正確に1240.00になることもないことを保証するチェック制約があります。
• anotherproductテーブルには、reducedpriceの値がpriceの値よりも大きくなることはないことを保証するチェック制約があります。
• secondtolastproductテーブルには、2つのチェック制約があります。1つはreducedpriceの値がpriceの値よりも大きくなることはないことを保証し、もう1つはtags配列に常に値productが含まれていることを保証します。
• lastproductテーブルには、categoryの値がclothingになることはないことを保証するチェック制約があります。

このセクションでは、データベースをイントロスペクションして、これらのテーブルのPrismaモデルを生成します。

注: チェック制約は現在、生成されたPrismaスキーマには含まれていません。ただし、基盤となるデータベースは引き続き制約を適用します。

開始するには、新しいNode.jsプロジェクトをセットアップし、prisma CLIを開発依存関係として追加します。

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

データベースをイントロスペクションするには、Prisma ORMに接続方法を伝える必要があります。これを行うには、Prismaスキーマで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.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を生成し、いくつかのサンプルクエリをデータベースに送信します。

まず、Prismaスキーマにgeneratorブロックを追加します（通常は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()

このコードでは、priceカラムに設定されたチェック制約を満たさない、価格が0.00の製品を作成しています。

次のコマンドでコードを実行します。

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

チェック制約はアルファベット順に解決され、最初に失敗した制約のみがエラーメッセージに表示されます。