CHECK制約によるデータ検証 (PostgreSQL)
概要
このページでは、PostgreSQLデータベースでチェック制約を設定する方法を説明します。チェック制約とは、値をテーブルに保存する前に満たさなければならない条件です。例えば、製品の割引価格は常に元の価格よりも低くなければならない、などです。
チェック制約は、テーブルの作成時（`CREATE TABLE`を使用）または既存のテーブルへの追加時（`ALTER TABLE`を使用）に適用できます。このガイドでは、これら4つの組み合わせすべてを扱います。
このガイドの最後では、データベースをイントロスペクトし、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`データベースに**単一の列に単一のチェック制約を持つ新しいテーブルを作成**します。
`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. 複数の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`という3つの列があり、以下のチェック制約があります。
- `tags`列（配列）には`product`というタグが含まれている必要があります。
- `reducedprice`の値は`price`の値よりも小さくなければなりません。
5. 既存のテーブルに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');
このコードには2つのSQLステートメントが含まれています。
- `lastproduct`という新しいテーブルを作成する
- テーブルを変更して`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`を持つテーブルをデータベースに作成しました。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 && npm install @prisma/client
データベースをイントロスペクトするには、Prisma ORMにデータベースへの接続方法を伝える必要があります。これは、Prismaスキーマで`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モデルを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`ブロックのすぐ下に追加します）。
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()
このコードでは、`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") }) }) })
チェック制約はアルファベット順に解決され、最初に失敗した制約のみがエラーメッセージに表示されます。