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 の情報スキーマの詳細について確認する。