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

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

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

次の 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 と StartTime を参照します。このチェックにより、コンサートの開始時間が終了時間よりも常に前であることが確認でき。ます

新しい行が挿入されたとき、または既存の行の StartTime または StartTime が更新されると、チェック制約式の値が評価されます。式が 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;

次のステップ

• Spanner の情報スキーマの詳細について確認する。