【MySQL】CHECK制約のススメ

リレーショナルデータベースでは、不整合のあるデータが登録されないように、制約 (CONSTRAINT) を設けることができます。
正しいデータが登録されるよう、ユーザーが入力するデータの内容をチェックして、ユーザーに対して適切にフィードバックしガイドするのは、アプリケーション側の役目です。一方で、アプリケーション側に考慮漏れや不具合があってもデータの整合性が保てるよう、適切な制約を定義するのは、データベース側の役割と考えます。

MySQL の CHECK制約

リレーショナルデータベースには、ユニーク制約、NOT NULL制約、外部参照制約、CHECK制約などの制約がありますが、ここでは CHECK制約を取り上げます。
MySQL では、バージョン 8.0.16 から CHECK制約が利用できるようになりました。

MySQL では、テーブル制約、カラム制約ともに、以下のような構文で利用できます。

[CONSTRAINT [symbol]] CHECK (expr)

オプションの symbol は制約の名前を指定します。(64文字以内)
省略するとシステム側で自動的に制約名が生成されます。

expr に制約として定義したい式を書きます。 INSERT や UPDATE のときに expr が TRUE もしくは UNKNOWN (NULL 値の場合) と評価されればデータを登録することができますが、 FALSE と評価されるとデータは登録されません。

この CHECK制約の構文は、具体的には以下のような使われ方をします。

MySQL のリファレンスに掲載されている例

CREATE TABLE t1
(
  CHECK (c1 <> c2),
  c1 INT CHECK (c1 > 10),
  c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
  c3 INT CHECK (c3 < 100),
  CONSTRAINT c1_nonzero CHECK (c1 <> 0),
  CHECK (c1 > c3)
);

CHECK 制約の使いどころ

CHECK 制約により具体的にどのような入力データの制限ができるのか、いくつか例示していきたいと思います。

数値の範囲指定

数値型のカラムに対して、許容される値の範囲を制限するような制約を記述することができます。
これにより仕様に合致しない不正な値が入力されるのを防ぎます。

整数の範囲 (年齢の制限)

例として整数型の年齢カラムが 18歳以上、120歳以下であることを指定します。

CREATE TABLE users (
    user_id INT PRIMARY KEY COMMENT 'ユーザーID',
    name VARCHAR(100) COMMENT '氏名',
    age INT COMMENT '年齢',
    CONSTRAINT check_age_range CHECK (age >= 18 AND age <= 120)
) COMMENT 'ユーザー';

数値範囲と刻み幅の指定 (靴のサイズ)

例として、靴のサイズが 20.0cm から 32.0cm まで 0.5cm 刻みで登録できることを指定します。数値の範囲と併せて、剰余を使って刻み幅の制約をかけています。

CREATE TABLE shoes (
    shoe_id INT PRIMARY KEY COMMENT '靴ID',
    model_name VARCHAR(100) COMMENT 'モデル名',
    shoe_size DECIMAL(3, 1) COMMENT '靴のサイズ (cm)',
    CONSTRAINT check_shoe_size CHECK (
        shoe_size >= 20.0 AND shoe_size <= 32.0 AND
        (shoe_size * 10) % 5 = 0  -- 0.5刻みであることを確認
    )
) COMMENT '靴';

正規表現による文字列の制限

文字列型のカラムに対して、特定のパターンに従うことを強制する場合に利用します。
これにより、フォーマットの不適切なデータが入力されるのを防ぎます。

メールアドレスのフォーマット

有効なメールアドレスのフォーマット¹に沿っていることを指定します。

CREATE TABLE customers (
    customer_id INT PRIMARY KEY COMMENT '顧客ID',
    customer_name VARCHAR(100) COMMENT '顧客名',
    email VARCHAR(255) COMMENT 'メールアドレス',
    CONSTRAINT check_email CHECK (email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$')
) COMMENT '顧客';

郵便番号のフォーマット

郵便番号のカラムが「3桁数字-4桁数字」の形式であることを指定します。

CREATE TABLE addresses (
    address_id INT PRIMARY KEY COMMENT '住所ID',
    address_name VARCHAR(255) COMMENT '住所',
    zip_code VARCHAR(10) COMMENT '郵便番号',
    CHECK (zip_code REGEXP '^\\d{3}-\\d{4}$')
) COMMENT '住所';

日付・時刻の特定条件の指定

日付または時刻型のカラムに対して、特定の条件を満たすことを強制する場合に利用します。

日付の曜日指定 (平日のみ)

配達日が平日である(=土曜日または日曜日ではない)こと。

CREATE TABLE orders (
    order_id INT PRIMARY KEY COMMENT '注文ID',
    customer_id INT COMMENT '顧客ID',
    order_date DATE COMMENT '注文日',
    delivery_date DATE COMMENT '配達日',
    CHECK (DAYOFWEEK(delivery_date) NOT IN (1, 7)) -- 1:日曜日, 7:土曜日
) COMMENT '注文';

日付の月初指定、月末指定

以下の例では、開始日が月初、終了日が月末であることを保証します。

CREATE TABLE contracts (
    contract_id INT PRIMARY KEY COMMENT '契約ID',
    name VARCHAR(100) COMMENT '契約名',
    start_date DATE COMMENT '契約開始日',
    end_date DATE COMMENT '契約終了日',
    CHECK (DAY(start_date) = 1), -- 月初(1日)のみ許可
    CHECK (end_date = LAST_DAY(end_date)) -- 月末のみ許可
) COMMENT '契約';

時刻の刻み幅の指定 (5分おきの場合)

日時のカラムが 5分刻みであること(かつ秒が0であること)を指定します。

CREATE TABLE appointments (
    appointment_id INT PRIMARY KEY COMMENT '予定ID',
    client_name VARCHAR(100) COMMENT '顧客名',
    appointment_time DATETIME COMMENT '予定日時',
    CHECK (MINUTE(appointment_time) % 5 = 0 AND SECOND(appointment_time) = 0)
) COMMENT '会合予定';

同じテーブルの別カラムとの関係性

同じテーブル内の複数のカラム間で、論理的な関係性を強制する場合に利用します。
これにより、カラム間の矛盾したデータ入力を防ぎます。

開始日と終了日の関係

開始日が終了日より前であることを保証します。

CREATE TABLE reservations (
    reservation_id INT PRIMARY KEY COMMENT '予約ID',
    room_id INT COMMENT '部屋ID',
    start_date DATE COMMENT '開始日',
    end_date DATE COMMENT '終了日',
    CHECK (start_date <= end_date) -- 開始日が終了日より前
) COMMENT '宿泊予約';

計算結果の範囲指定

単価×数量の金額範囲が定められている場合など、カラム同士の計算結果に対して制限をかけます。

CREATE TABLE order_details (
    order_detail_id INT PRIMARY KEY COMMENT '注文明細ID',
    order_id INT COMMENT '注文ID',
    product_id INT COMMENT '商品ID',
    quantity INT COMMENT '数量',
    unit_price DECIMAL(10, 2) COMMENT '金額',
    CHECK (unit_price * quantity >= -100000 AND unit_price * quantity <= 100000)
    -- 各注文明細は -100,000以上、100,000以下であること
);

まとめ

いかがでしょうか。
データの整合性を守る最後の砦として、CHECK 制約をぜひ活用してみてください。

1. このメールアドレスの正規表現は RFC的に有効なメールアドレスを完全に網羅するものではありません。しかしながら、多くの一般的なメールアドレスの形式をカバーし、入力ミスを防ぐのに十分役立ちます。 ↩