チェック制約を作成、管理する

CHECK 制約を使用すると、1 つ以上の列の値がブール式を満たす必要があることを指定できます。このページでは、GoogleSQL 言語データベースと PostgreSQL 言語データベースでこのタイプの制約を追加して管理する方法について説明します。

新しいテーブルへのチェック制約の追加

次の CREATE TABLE スニペットでは、コンサートに関する情報を格納するテーブルを作成します。コンサートの終了時間が開始時刻よりも後であることを条件にするには、チェック制約を含めます。

GoogleSQL

CREATE TABLE Concerts (
  ConcertId INT64,
  StartTime Timestamp,
  EndTime Timestamp,
  CONSTRAINT start_before_end CHECK(StartTime < EndTime),
) PRIMARY KEY (ConcertId);

PostgreSQL

CREATE TABLE Concerts (
  ConcertId BIGINT,
  StartTime TIMESTAMPTZ,
  EndTime TIMESTAMPTZ,
  CONSTRAINT start_before_end CHECK(StartTime < EndTime),
  PRIMARY KEY (ConcertId)
);

制約の定義は CONSTRAINT キーワードで始まります。この例では、制約を明示的に start_before_end と命名しています。これにより、エラー メッセージ内を簡単に識別でき、それを参照する必要が出てきます。名前を指定しないと、Spanner は生成された名前の先頭に接頭辞 CK_ を付けることで名前を指定します。制約名は、テーブルとインデックスの名前によるスキーマの範囲に収まり、スキーマ内で一意である必要があります。チェック制約の定義は、キーワード CHECK と、それに続く式で構成されます。式では、このテーブルの列のみを参照できます。この例では、StartTime と EndTime を参照します。このチェック制約により、コンサートの開始時間が終了時間よりも常に前であることが確認できます。

新しい行が挿入されたとき、または既存の行の StartTime または EndTime が更新されたときに、チェック制約式の値が評価されます。式が TRUE または NULL と評価された場合、チェック制約によってデータの変更が許可されます。式が FALSE と評価された場合、データの変更は許可されません。

  • チェック制約の expression 条件には以下の制限が適用されます。

    • 式は、同じテーブルの列のみを参照できます。

    • 式では、少なくとも 1 つの生成されていない列を参照する必要があります。その際は、生成されていない列を直接参照するか、生成されていない列を参照する生成された列を通じて参照します。

    • この式では、allow_commit_timestamp オプションが設定されている列を参照できません。

    • 式にサブクエリを含めることはできません。

    • 式に CURRENT_DATE() や CURRENT_TIMESTAMP() などの非決定性関数を含めることはできません。

既存のテーブルへのチェック制約の追加

次の ALTER TABLE ステートメントを使用して、すべてのコンサート ID がゼロより大きいことを確認するための制約を追加します。

ALTER TABLE Concerts
ADD CONSTRAINT concert_id_gt_0 CHECK (ConcertId > 0);

この場合も、制約に concert_id_gt_0 という名前を付けています。既存のテーブルに CHECK 制約を追加すると、新しいデータに対して直ちに制約の適用が開始され、既存のデータが新しい制約を遵守していることを検証する長時間実行オペレーションが開始されます。この検証は長時間実行オペレーションとして実行されるため、テーブル上のトランザクションは影響を受けません。詳細については、スキーマ更新のパフォーマンスをご覧ください。既存のデータの違反があった場合は、その制約はロールバックされます。

チェック制約を削除する

次の DDL ステートメントは、Concerts テーブルから CHECK 制約を削除します。

ALTER TABLE Concerts
DROP CONSTRAINT concert_id_gt_0;

チェック制約式を変更する

CHECK 制約の式は変更できません。代わりに、既存の制約を削除して、新しい式で新しい制約を作成する必要があります。

チェック制約のプロパティを表示する

Spanner の INFORMATION_SCHEMA には、データベースに対するチェック制約に関する情報が含まれています。以下に、情報スキーマをクエリすることによって回答できる質問の例を示します。

データベースで定義されているチェック制約

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'CHECK';

データベースにおけるチェック制約の現在の状態

既存のテーブルにチェック制約を追加した場合は、現在の状態を表示すると、既存のすべてのデータが制約に対して検証されているかどうかを確認できます。次のクエリでは、SPANNER_STATE が VALIDATING_DATA を返す場合、Spanner はその制約に対して既存のデータを検証する処理がまだ進行中であることを示しています。

SELECT cc.CONSTRAINT_NAME, cc.SPANNER_STATE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS as cc;

次のステップ