PostgreSQLでデータの整合性を保つ：制約の活用方法

2024-05-21

PostgreSQLで列の値を制限する方法

データ型

列のデータ型を選択することで、その列に格納できる値の種類を制限することができます。例えば、age という列を定義する場合、int 型を選択すると、その列には整数値のみが格納されます。他の一般的なデータ型としては、varchar（文字列）、date（日付）、boolean（真偽値）などがあります。

デフォルト値

列にデフォルト値を設定することで、その列に新しい値が挿入されない場合に、自動的に割り当てられる値を指定することができます。例えば、created_at という列に CURRENT_TIMESTAMP というデフォルト値を設定すると、その列には新しい行が挿入されるたびに、自動的に現在時刻が割り当てられます。

チェック制約を使用して、列の値が特定の条件を満たしていることを確認することができます。例えば、age という列に CHECK (age >= 18) というチェック制約を設定すると、その列には18歳以上の値のみが格納されます。

外部キー制約を使用して、ある列の値が別の表の列に存在する値を参照していることを確認することができます。例えば、orders という表に customer_id という列があり、customers という表に id という列がある場合、orders 表の customer_id 列に customers 表の id 列を外部キー制約として設定することができます。これにより、orders 表に存在するすべての customer_id は、customers 表に存在する id と一致していることが保証されます。

トリガーを使用して、列の値が変更されたときに、自動的にアクションを実行することができます。例えば、orders という表に status という列があり、その列の値が shipped に変更されたときに、出荷通知メールを送信するトリガーを作成することができます。

これらの方法は、単独で、または組み合わせて使用することができます。具体的な方法は、要件によって異なります。

例

以下の例では、customers という表に3つの列を作成します。

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE CHECK (email LIKE '%@%')
);

この例では、以下の制約が使用されています。

id 列は、シリアル型であり、プライマリキーです。
name 列は、NOT NULL 制約があり、50文字以下の文字列しか格納できません。
email 列は、UNIQUE 制約があり、@ 記号を含む100文字以下の文字列しか格納できません。

この制約により、customers 表に格納されるデータの整合性と信頼性が保たれます。

PostgreSQLでは、様々な制約を使用して、列の値を制限することができます。データ型、デフォルト値、チェック制約、外部キー制約、トリガーなどを適切に組み合わせることで、要件に合ったデータベースを設計することができます。

PostgreSQL 制約のサンプルコード

データ型

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT CHECK (age >= 18)
);

age 列は、整数型であり、CHECK 制約により、18歳以上の値のみが格納されます。

デフォルト値

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  order_date DATE NOT NULL DEFAULT CURRENT_DATE
);

customer_id 列は、整数型であり、NOT NULL 制約があり、customers 表の id 列を参照する外部キー制約があります。
order_date 列は、日付型であり、NOT NULL 制約があり、デフォルト値として現在の日付が設定されています。

チェック制約

ALTER TABLE products
ADD CHECK (price >= 0);

この例では、products 表の price 列にチェック制約を追加します。この制約により、price 列には0以上の値のみが格納されます。

外部キー制約

ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

この例では、orders 表の customer_id 列に外部キー制約を追加します。この制約により、orders 表に存在するすべての customer_id は、customers 表に存在する id と一致していることが保証されます。

トリガー

CREATE TRIGGER notify_shipped_orders
AFTER UPDATE ON orders
FOR EACH ROW
WHEN NEW.status = 'shipped'
BEGIN
  SEND EMAIL TO customer_email(@NEW.customer_id), 'Your order has been shipped!';
END;

この例では、orders 表の status 列が shipped に変更されたときに、出荷通知メールを送信するトリガーを作成します。

これらの例はほんの一例であり、PostgreSQLで制約を定義する方法は他にもたくさんあります。詳細については、PostgreSQLのドキュメントを参照してください。

PostgreSQLで列の値を制限するその他の方法

排他制約を使用して、列に格納できる値のセットを制限することができます。例えば、gender という列に 'male' と 'female' のみを格納できるように、排他制約を設定することができます。

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  gender CHAR(1) CHECK (gender IN ('m', 'f')),
  email VARCHAR(100) UNIQUE
);

サブクエリ制約を使用して、列の値が別のクエリの結果と一致していることを確認することができます。例えば、orders という表に product_id という列があり、products という表に id という列がある場合、orders 表の product_id 列に products 表の id 列をサブクエリ制約として設定することができます。

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  product_id INT NOT NULL CHECK (product_id IN (SELECT id FROM products)),
  order_date DATE NOT NULL DEFAULT CURRENT_DATE
);

定義済みデータ型

PostgreSQLには、日付、時間、時刻、区間、および地理空間データなどの特定の種類のデータを表すために使用できる、様々な定義済みデータ型が用意されています。これらのデータ型を使用すると、列に格納できる値をより細かく制御することができます。

ルーチン制約を使用して、列の値がユーザー定義のルーチンによって評価されることを確認することができます。これにより、より複雑な検証ロジックを実装することができます。

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  price NUMERIC(10,2) NOT NULL CHECK (valid_price(price)),
  stock INT NOT NULL
);

CREATE FUNCTION valid_price(price NUMERIC)
RETURNS BOOLEAN
AS $$
BEGIN
  RETURN price >= 0.01 AND price <= 10000.00;
END; $$ LANGUAGE plpgsql;

制約トリガーを使用して、制約が違反された場合にアクションを実行することができます。例えば、orders 表の order_date 列の値が過去の日付である場合、エラーメッセージをログに記録する制約トリガーを作成することができます。

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  product_id INT NOT NULL REFERENCES products(id),
  order_date DATE NOT NULL,
  CONSTRAINT check_order_date CHECK (order_date >= CURRENT_DATE)
);

CREATE CONSTRAINT TRIGGER order_date_violation_trigger
AFTER UPDATE ON orders
FOR EACH ROW
WHEN NEW.order_date < CURRENT_DATE
BEGIN
  INSERT INTO logs (message) VALUES ('Order date cannot be in the past.');
  RAISE EXCEPTION 'Invalid order date';
END;