Membuat dan mengelola batasan pemeriksaan

Batasan CHECK memungkinkan Anda menentukan bahwa nilai satu atau beberapa kolom harus memenuhi ekspresi boolean. Dalam artikel ini, kami akan menjelaskan cara mengelola jenis batasan ini di database Anda.

Menambahkan batasan pemeriksaan ke tabel baru

Dalam cuplikan CREATE TABLE berikut, kita membuat tabel untuk menyimpan informasi tentang konser. Untuk mewajibkan waktu akhir konser lebih lambat dari waktu mulainya, kita menyertakan batasan pemeriksaan.

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)
);

Definisi batasan dimulai dengan kata kunci CONSTRAINT. Kami telah secara eksplisit menamai batasan sebagai start_before_end dalam contoh ini, yang memudahkan untuk mengidentifikasi dalam pesan error dan setiap kali kita perlu merujuknya. Jika tidak ada nama yang diberikan, Spanner akan memberikannya, dengan nama yang dihasilkan dimulai dengan awalan CK_. Nama batasan dicakup dalam skema, beserta nama untuk tabel dan indeks, dan harus unik dalam skema. Definisi batasan pemeriksaan terdiri dari kata kunci CHECK, diikuti dengan ekspresi dalam tanda kurung. Ekspresi hanya dapat mereferensikan kolom tabel ini. Dalam contoh ini, kode ini mereferensikan StartTime dan EndTime, dan pemeriksaan di sini memastikan bahwa waktu mulai konser selalu kurang dari waktu akhir.

Nilai ekspresi batasan pemeriksaan dievaluasi saat baris baru disisipkan atau saat StartTime atau EndTime dari baris yang ada diperbarui. Jika ekspresi dievaluasi menjadi TRUE atau NULL, perubahan data diizinkan oleh batasan pemeriksaan. Jika ekspresi bernilai FALSE, perubahan data tidak diizinkan.

  • Batasan berikut berlaku untuk istilah expression batasan pemeriksaan.

    • Ekspresi hanya dapat mereferensikan kolom dalam tabel yang sama.

    • Ekspresi harus mereferensikan minimal satu kolom yang tidak dibuat, baik secara langsung maupun melalui kolom yang dibuat yang mereferensikan kolom yang tidak dibuat.

    • Ekspresi tidak dapat mereferensikan kolom yang telah menetapkan opsi allow_commit_timestamp.

    • Ekspresi tidak boleh berisi subkueri.

    • Ekspresi tidak boleh berisi fungsi non-deterministik, seperti CURRENT_DATE() dan CURRENT_TIMESTAMP().

Menambahkan batasan pemeriksaan ke tabel yang ada

Dengan menggunakan pernyataan ALTER TABLE berikut, kita menambahkan batasan untuk memastikan semua ID konser lebih besar dari nol.

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

Sekali lagi, kita telah memberi batasan nama, concert_id_gt_0. Menambahkan batasan CHECK ke tabel yang ada akan segera memulai penerapan batasan untuk data baru dan memulai operasi yang berjalan lama untuk memvalidasi bahwa data yang ada sesuai dengan batasan baru. Karena validasi ini dilakukan sebagai operasi yang berjalan lama, transaksi yang sedang berlangsung di tabel tidak akan terpengaruh. Untuk mengetahui informasi selengkapnya, lihat Performa update skema. Jika ada pelanggaran pada data yang ada, batasan akan di-roll back.

Menghapus batasan pemeriksaan

Pernyataan DDL berikut menghapus batasan CHECK dari tabel Concerts.

ALTER TABLE Concerts
DROP CONSTRAINT concert_id_gt_0;

Mengubah ekspresi batasan pemeriksaan

Mengubah ekspresi batasan CHECK tidak diizinkan. Sebagai gantinya, Anda harus menghapus batasan yang ada dan membuat batasan baru dengan ekspresi baru.

Melihat properti batasan pemeriksaan

INFORMATION_SCHEMA Spanner berisi informasi tentang batasan pemeriksaan pada database Anda. Berikut adalah beberapa contoh pertanyaan yang dapat Anda jawab dengan membuat kueri skema informasi.

Batasan pemeriksaan apa yang ditentukan dalam database saya?

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

Apa status batasan pemeriksaan saat ini di database saya?

Jika telah menambahkan batasan pemeriksaan ke tabel yang ada, Anda mungkin ingin melihat statusnya saat ini untuk menentukan, misalnya, apakah semua data yang ada telah divalidasi terhadap batasan. Jika SPANNER_STATE menampilkan VALIDATING_DATA dalam kueri berikut, artinya Spanner masih dalam proses memvalidasi data yang ada terhadap batasan tersebut.

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

Langkah selanjutnya