💂

【MySQL】CHECK制約を使って不正なデータからテーブルを守ろう

2024/04/27に公開

MySQL

tech

この記事について

最近、チーム内のMySQLのバージョンを５系から８系にバージョンアップしました。ですが、MySQL８系の恩恵といえば、Geographic Information System(GIS)以外受けてないような気がするなと思いました。
最近恩恵を受けたのでそちらをまとめようと思います。

先に結論を３つ

MySQL 8.0.16からCHECK 制約が追加された
記述した条件式に合わない行の挿入・更新を防ぐことができる
適用されないパターンもあるので使う時は注意が必要

CHECK 制約

CHECK制約は、テーブルにデータを挿入、または更新する際に条件を満たすか検証し、もし満たさない場合はエラーにしてしまう機能です。例えば、以下のような事例で活用できます。
- 0~255の数値を扱うカラムを0~10までしか挿入しないようにしたい
- 未成年のユーザーが登録されることを防ぐために、年齢を18歳未満の値を弾きたい
- 特定の文字列は受け付けないようにしたい

この時にCHECK制約を使うことができます。

CHECK制約の使い方

CHECK制約はテーブルに対して設定します。

テーブル作成時に使用する場合

  CREATE TABLE `company_user` (
      `company_ulid` VARCHAR(26) NOT NULL COMMENT '企業のULID'
      `user_ulid` VARCHAR(26) NOT NULL COMMENT 'ユーザーのULID',
      , `created_id` BIGINT(20) COMMENT '登録者id'
      , `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録日時'
      , PRIMARY KEY (`company_ulid`, `user_ulid`)
      , FOREIGN KEY (`company_ulid`) REFERENCES `company` (`ulid`)
      , FOREIGN KEY (`user_ulid`) REFERENCES `user` (`ulid`)
      , CHECK (`制約を記載する`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci comment='企業とユーザーの紐付けテーブル';

既存のテーブルに制約を追加する場合

    ALTER TABLE `company_user` ADD CONSTRAINT `user_check` CHECK (`制約を記載する`);

実践

制約の定義

今回は複合主キーのカラムに同じ値が入らないような制約をつけてみます

  CREATE TABLE `company_user` (
      `company_ulid` VARCHAR(26) NOT NULL COMMENT '企業のULID'
      `user_ulid` VARCHAR(26) NOT NULL COMMENT 'ユーザーのULID',
      , `created_id` BIGINT(20) COMMENT '登録者id'
      , `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録日時'
      , PRIMARY KEY (`company_ulid`, `user_ulid`)
      , FOREIGN KEY (`company_ulid`) REFERENCES `company` (`ulid`)
      , FOREIGN KEY (`user_ulid`) REFERENCES `user` (`ulid`)
      , CHECK (`company_ulid` <> `user_ulid`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci comment='企業とユーザーの紐付けテーブル';

CHECK (company_ulid <> user_ulid)でcompany_ulidとuser_ulidが等しくないことを制約として設定することができます

動作確認

以下のクエリを実行し、結果がどうなるかチェックします

INSERT INTO company_user (
    company_ulid, 
    user_ulid,
    created_id
) VALUES (
	"01G0CADQF1A9MKQ0QAVBF24E6D", 
	"01G0CADQF1A9MKQ0QAVBF24E6D",
	1
);

mysql > ERROR 3819 (HY000): Check constraint 'company_user_chk_1' is violated.

同じ値が挿入されることを弾いてくれました。これを活用することで、アプリケーション側で万が一弾けなかった値もSQL側で弾くことが可能です

適用されないパターン

InnoDBストレージエンジンが使用されていない場合
単一の CHECK 制約には単一の条件しか指定できません。以下のように複数の列に対する複合条件を指定することはできません。

CREATE TABLE table_name (
    column1 INT,
    column2 INT,
    CHECK (column1 > 0 AND column2 < 100)
);

独立させれば問題ないです

CREATE TABLE table_name (
    column1 INT CHECK (column1 > 0),
    column2 INT CHECK (column2 < 100)
);

公式ドキュメントより
- AUTO_INCREMENT 属性を持つカラムおよび他のテーブルのカラムを除き、生成されていないカラムおよび生成されたカラムは許可されます。
リテラル、決定的組込み関数および演算子を使用できます。関数は、テーブル内の同じデータが指定された場合、接続ユーザーとは関係なく、複数の起動で同じ結果が生成される場合は決定論的です。非決定的で、この定義に失敗する関数の例: CONNECTION_ID(), CURRENT_USER(), NOW()。
ストアドファンクションおよびユーザー定義関数は使用できません。
ストアドプロシージャおよびストアドファンクションのパラメータは使用できません。
変数 (システム変数、ユーザー定義変数およびストアドプログラムローカル変数) は使用できません。
サブクエリーは許可されません。

最後に

今回はMySQL８系で追加された CHECK制約についてまとめました。
PostgreSQLでも同様の機能が存在するようです。
- 5.3. 制約
今後も活用していきたいですし、MySQL8系の恩恵を受けられるように調査を続けます。

この記事について

先に結論を３つ

CHECK 制約

CHECK制約の使い方

実践

制約の定義

動作確認

適用されないパターン

最後に

Discussion