PostgreSQLでメールアドレスの有効性を検証する制約の作り方

2024-06-14

PostgreSQLで電子メールアドレスの有効性を検証する制約の作成

要件

このチュートリアルを完了するには、以下のものが必要です。

PostgreSQLデータベースサーバーがインストールおよび実行されている
PostgreSQLデータベースにアクセスできるユーザーアカウント

手順

電子メールアドレス列を作成する

まず、users テーブルなどの既存のテーブルに電子メールアドレス列を作成する必要があります。次の例では、email という名前の列を作成します。

ALTER TABLE users
ADD COLUMN email VARCHAR(255) NOT NULL;

電子メールアドレスの書式を検証する正規表現チェックを作成する

次に、有効な電子メールアドレス形式を定義する正規表現チェックを作成する必要があります。次の例では、check_email_format という名前のチェックを作成します。

CREATE CHECK CONSTRAINT check_email_format
CHECK (email ~ '^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');

このチェックは、次の条件を満たす電子メールアドレスのみを有効とします。

少なくとも1つの英字または数字で始まり、その後、ピリオド（.）と、2文字以上の英字からなるドメインを含む
アットマーク (@) 記号を含む
ローカル部分とドメイン部分に、特殊文字 (!#$%&'*+/=?^_{|}~-`) が含まれていてもよい

最後に、作成したチェックをemail 列に適用する必要があります。

ALTER TABLE users
ADD CONSTRAINT fk_email_user FOREIGN KEY (email) REFERENCES users(email);

これで、users テーブルに挿入されるすべての電子メールアドレスが有効な形式であることが保証されます。

補足

上記の例で使用されている正規表現は、単純なものです。より複雑な検証要件がある場合は、それに応じて正規表現を調整する必要があります。

また、この方法は、電子メールアドレスの所有権を検証するものではありません。つまり、[email protected] という電子メールアドレスが実際に存在し、ユーザーに属していることを確認するものではありません。電子メールアドレスの所有権を検証するには、追加の検証手順が必要です。

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);

このコードは、users という名前のテーブルを作成し、id 列を主キーとして、name 列と email 列を追加します。 email 列は、NOT NULL 制約と UNIQUE 制約が設定されています。つまり、email 列は空にすることはできず、すべての値が一意である必要があります。

CREATE CHECK CONSTRAINT check_email_format
CHECK (email ~ '^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');

このコードは、check_email_format という名前のチェックを作成し、有効な電子メールアドレス形式を定義する正規表現を指定します。この正規表現は、上記のチュートリアルで説明した条件を満たす電子メールアドレスのみを有効とします。

ALTER TABLE users
ADD CONSTRAINT fk_email_user FOREIGN KEY (email) REFERENCES users(email);

このコードは、作成したチェックをemail 列に適用します。これにより、users テーブルに挿入されるすべての電子メールアドレスが有効な形式であることが保証されます。

説明

このサンプルコードは、基本的な例です。実際のアプリケーションでは、要件に応じてコードを調整する必要があります。たとえば、次のことができます。

より複雑な正規表現を使用して、電子メールアドレス形式の検証を強化する
電子メールアドレスの所有権を検証する追加の検証手順を追加する
複数の電子メールアドレス列を持つテーブルを作成する

注意事項

このサンプルコードは、PostgreSQL 10.0以降で使用できます。
制約を作成する前に、テーブルに既存のデータがないことを確認してください。

サブクエリを使用して制約を作成する

この方法は、より複雑な検証ロジックが必要な場合に役立ちます。

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE CHECK (email @> '^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$')
);

この例では、email @> '^[a-zA-Z0-9.!#$%&'*+/=?^_{|}~-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}$'というサブクエリを使用して、電子メールアドレス形式を検証しています。このサブクエリは、email` 列の値が正規表現に一致するかどうかを評価します。

PostgreSQL 11以降では、email データ型を使用して、電子メールアドレス列を定義できます。このデータ型は、電子メールアドレス形式の自動検証をサポートします。

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email email NOT NULL UNIQUE
);

この例では、email 列を email データ型として定義しています。これにより、email 列の値が有効な電子メールアドレス形式であることが自動的に検証されます。

各方法の比較

方法	利点	欠点
正規表現チェック	複雑な検証ロジックに対応可能	正規表現の知識が必要
サブクエリを使用した制約	柔軟性が高い	複雑で読みづらいコードになる可能性がある
`email` データ型	シンプルで使いやすい	PostgreSQL 11以降でのみ使用可能

最適な方法の選択