Exclusion Constraints

Primary key dan unique constraint menjaga satu jenis ketertiban: tidak boleh ada dua baris dengan nilai yang sama. Namun banyak aturan bisnis tidak berbentuk kesamaan nilai. Satu dokter tidak boleh punya dua shift yang tumpang tindih waktunya; satu tarif kamar tidak boleh punya dua masa berlaku yang saling menjalin. Kata kuncinya bukan =, melainkan relasi seperti “tumpang tindih” — dan CHECK pun tak bisa menolong karena CHECK hanya boleh membaca barisnya sendiri, sedangkan tumpang tindih adalah hubungan antar baris. PostgreSQL punya perangkat yang tepat untuk ini: exclusion constraint, generalisasi dari unique constraint yang menolak dua baris bila keduanya berkait lewat operator yang kita pilih sendiri. Pada chapter ini kita memakainya untuk jadwal dokter yang bebas bentrok, membedah mengapa CHECK gagal mengerjakan pekerjaan yang sama, lalu memakai exclusion di atas btree untuk keunikan atas hasil ekspresi.

Generalisasi dari Unique Constraint #

Bentuk deklarasinya: EXCLUDE USING <metode_index> (kolom WITH operator, ...). Aturannya berbunyi: sebuah baris baru ditolak bila ada baris lama yang memenuhi semua operator terhadap baris baru itu. Unique constraint pada dasarnya adalah kasus khususnya — UNIQUE (a, b) setara dengan EXCLUDE (a WITH =, b WITH =). Kekuatan sebenarnya muncul saat operatornya bukan =: operator && (overlap) untuk range type, atau operator lain yang didukung metode index-nya.

Kata USING gist menunjuk metode index yang dipakai untuk memeriksa konflik secara cepat, dan di sinilah satu syarat teknis muncul: semua kolom dalam constraint harus punya operator class untuk metode itu. Tipe range seperti tstzrange mendukung GiST secara bawaan, tetapi kolom skalar seperti integer tidak — ekstensi btree_gist yang mengisi kekosongan itu.

Studi Kasus: Jadwal Dokter Tanpa Bentrok #

Tabel jadwal_dokter menyimpan shift sebagai tstzrange — rentang waktu ber-zona-waktu yang sudah kita kenal di Section Tipe Data. Constraint-nya menyatakan aturan bisnis dalam satu baris deklarasi: untuk dokter yang sama (dokter_id WITH =), tidak boleh ada shift yang tumpang tindih (shift WITH &&):

-- Satu dokter tidak boleh punya dua shift yang tumpang tindih
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE jadwal_dokter (
    id        integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    dokter_id integer NOT NULL,
    poli      text NOT NULL,
    shift     tstzrange NOT NULL,
    EXCLUDE USING gist (dokter_id WITH =, shift WITH &&)
);

-- Shift pagi dan siang dokter 1 hanya bersentuhan di tepi: sah
INSERT INTO jadwal_dokter (dokter_id, poli, shift) VALUES
    (1, 'P-101', tstzrange('2026-03-02 08:00+07', '2026-03-02 12:00+07', '[)')),
    (1, 'P-101', tstzrange('2026-03-02 13:00+07', '2026-03-02 16:00+07', '[)')),
    (2, 'P-102', tstzrange('2026-03-02 08:00+07', '2026-03-02 12:00+07', '[)'));

SELECT id, dokter_id, poli, shift
FROM jadwal_dokter
ORDER BY id;

Output yang Dihasilkan:

CREATE EXTENSION
CREATE TABLE
INSERT 0 3
 id | dokter_id | poli  |                        shift                        
----+-----------+-------+-----------------------------------------------------
  1 |         1 | P-101 | ["2026-03-02 08:00:00+07","2026-03-02 12:00:00+07")
  2 |         1 | P-101 | ["2026-03-02 13:00:00+07","2026-03-02 16:00:00+07")
  3 |         2 | P-102 | ["2026-03-02 08:00:00+07","2026-03-02 12:00:00+07")
(3 rows)

Mari kita bedah deklarasinya:

1. CREATE EXTENSION IF NOT EXISTS btree_gist: memuat operator class GiST untuk tipe-tipe skalar. Tanpa ekstensi ini, EXCLUDE USING gist yang memuat kolom integer ditolak dengan error “no operator found”. Ekstensi cukup dipasang sekali per database.
2. EXCLUDE USING gist (dokter_id WITH =, shift WITH &&): dua kolom, dua operator. Sebuah konflik terjadi hanya bila keduanya terpenuhi terhadap baris yang sama: dokternya sama DAN periodenya bertumpang. Dokter berbeda boleh jalan pada jam yang sama; dokter sama boleh jalan berurutan.
3. Tiga INSERT diterima: shift [08:00, 12:00) dan [13:00, 16:00) milik dokter 1 hanya berbagi tepi eksklusif — operator && tidak menganggapnya bertumpang, sehingga keduanya sah. Baris dokter 2 pada jam yang sama dengan dokter 1 juga sah karena dokter_id WITH = tidak terpenuhi.
4. id identity column: penomoran otomatis dari chapter sebelumnya tetap bekerja seperti biasa — exclusion constraint tidak mengganggu mekanisme lain.

Saat sebuah baris baru benar-benar menabrak, server menolaknya dengan pesan yang menyebut pasangan kunci yang berkonflik:

-- 11:00-14:00 menabrak shift pagi (sampai 12:00) dokter yang sama
INSERT INTO jadwal_dokter (dokter_id, poli, shift) VALUES
    (1, 'P-101', tstzrange('2026-03-02 11:00+07', '2026-03-02 14:00+07', '[)'));

Output yang Dihasilkan:

psql:shift-bentrok.sql:3: ERROR:  conflicting key value violates exclusion constraint "jadwal_dokter_dokter_id_shift_excl"
DETAIL:  Key (dokter_id, shift)=(1, ["2026-03-02 11:00:00+07","2026-03-02 14:00:00+07")) conflicts with existing key (dokter_id, shift)=(1, ["2026-03-02 08:00:00+07","2026-03-02 12:00:00+07")).

Shift baru [11:00, 14:00) bertabrakan dengan shift pagi [08:00, 12:00) — irisan 11:00 sampai 12:00 cukup bagi operator && untuk berkata ya, dan dokter_id yang sama menutup kasusnya. Server memblokir seluruh barisnya: tidak ada setengah shift yang tersimpan. Pola [) (batas bawah inklusif, batas atas eksklusif) yang dipakai di seluruh contoh membuat shift berurutan menempel rapi tanpa tumpang tindih satu detik pun — kebiasaan notasi yang dibahas tuntas di chapter Range dan Multirange Types.

Mengapa CHECK Tidak Bisa #

Ide pertama banyak orang (dan resep lama di forum): pakai CHECK yang memeriksa tabelnya sendiri dengan subquery. PostgreSQL menolaknya di akar:

-- CHECK hanya boleh membaca barisnya sendiri: subquery ditolak
CREATE TABLE jadwal_salah (
    id        integer PRIMARY KEY,
    dokter_id integer NOT NULL,
    shift     tstzrange NOT NULL,
    CHECK (NOT EXISTS (
        SELECT 1 FROM jadwal_salah lain
        WHERE lain.dokter_id = jadwal_salah.dokter_id
          AND lain.shift && jadwal_salah.shift
    ))
);

Output yang Dihasilkan:

psql:check-subquery.sql:11: ERROR:  cannot use subquery in check constraint
LINE 5:     CHECK (NOT EXISTS (
                       ^

Error cannot use subquery in check constraint bukan keterbatasan yang kebetulan ada. Sebuah CHECK harus dapat diverifikasi dari isi baris itu sendiri kapan pun barisnya ditulis — begitu pemeriksaannya melihat baris lain, kebenarannya bergantung pada keadaan tabel yang bisa berubah setelahnya (baris lain masuk, dihapus, atau diubah). Exclusion constraint dirancang untuk relasi antar baris: pemeriksaannya memakai index GiST yang diperbarui pada setiap tulis, sehingga jaminannya selalu segar. Perbandingan ketiganya:

EXCLUDE di Atas Btree: Keunikan Ekspresi #

Metode index tidak harus GiST. EXCLUDE USING btree bekerja dengan operator yang didukung btree — terutama = — dan punya satu kelebihan yang tidak dimiliki UNIQUE biasa: kolomnya boleh berupa ekspresi. UNIQUE hanya menerima daftar kolom polos, sedangkan exclusion menerima hasil fungsi. Kasus klasiknya: email yang harus unik tanpa membedakan huruf besar dan kecil:

-- EXCLUDE di atas btree: keunikan atas hasil ekspresi
CREATE TABLE pengguna (
    id    integer PRIMARY KEY,
    email text NOT NULL,
    EXCLUDE USING btree (lower(email) WITH =)
);

INSERT INTO pengguna VALUES (1, '[email protected]');

SELECT id, email, lower(email) AS email_baku
FROM pengguna
ORDER BY id;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 1
 id |      email       |    email_baku    
----+------------------+------------------
  1 | [email protected] | [email protected]
(1 row)

Constraint EXCLUDE USING btree (lower(email) WITH =) menyatakan: tidak boleh ada dua baris yang lower(email)-nya sama. Email pertama tersimpan apa adanya, dan kolom email_baku pada hasil hanyalah lower(email) yang dihitung saat query — bukan kolom tersimpan. Ujian keunikannya bekerja persis seperti yang diharapkan:

-- Huruf besar/kecil berbeda, tapi lower(email) sama persis: ditolak
INSERT INTO pengguna VALUES (2, '[email protected]');

Output yang Dihasilkan:

psql:email-bentrok.sql:2: ERROR:  conflicting key value violates exclusion constraint "pengguna_lower_excl"
DETAIL:  Key (lower(email))=([email protected]) conflicts with existing key (lower(email))=([email protected]).

Teks [email protected] berbeda dari [email protected] byte demi byte — UNIQUE (email) biasa akan menerimanya sebagai dua email berbeda. Tetapi hasil lower() keduanya identik, dan exclusion constraint di atas btree menolak pendaftaran ganda itu. Pesan errornya menampilkan kunci dalam bentuk ekspresinya, Key (lower(email))=([email protected]), sehingga penyebab penolakan langsung terbaca. Alternatif lain untuk kebutuhan yang sama adalah generated column lower(email) yang dipasangi UNIQUE (chapter Generated Columns) — pilihan mana yang lebih pas bergantung pada apakah versi lowercase-nya layak disimpan sebagai kolom.

Sejauh ini seluruh constraint kita dideklarasikan saat CREATE TABLE pertama kali dijalankan. Di dunia nyata tabel sudah lebih dulu berisi data ketika aturan baru harus dipasang: foreign key yang baru disadari perlu, aturan unik yang menyusul, sampai constraint yang harus dipasang tanpa menghentikan operasi. ALTER TABLE mengelola seluruh daur hidup constraint itu — topik penutup section ini.

Referensi #

• Constraints pada CREATE TABLE
• CREATE TABLE Reference
• btree_gist

-- Satu dokter tidak boleh punya dua shift yang tumpang tindih
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE jadwal_dokter (
    id        integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    dokter_id integer NOT NULL,
    poli      text NOT NULL,
    shift     tstzrange NOT NULL,
    EXCLUDE USING gist (dokter_id WITH =, shift WITH &&)
);

-- Shift pagi dan siang dokter 1 hanya bersentuhan di tepi: sah
INSERT INTO jadwal_dokter (dokter_id, poli, shift) VALUES
    (1, 'P-101', tstzrange('2026-03-02 08:00+07', '2026-03-02 12:00+07', '[)')),
    (1, 'P-101', tstzrange('2026-03-02 13:00+07', '2026-03-02 16:00+07', '[)')),
    (2, 'P-102', tstzrange('2026-03-02 08:00+07', '2026-03-02 12:00+07', '[)'));

SELECT id, dokter_id, poli, shift
FROM jadwal_dokter
ORDER BY id;

-- CHECK hanya boleh membaca barisnya sendiri: subquery ditolak
CREATE TABLE jadwal_salah (
    id        integer PRIMARY KEY,
    dokter_id integer NOT NULL,
    shift     tstzrange NOT NULL,
    CHECK (NOT EXISTS (
        SELECT 1 FROM jadwal_salah lain
        WHERE lain.dokter_id = jadwal_salah.dokter_id
          AND lain.shift && jadwal_salah.shift
    ))
);

-- Huruf besar/kecil berbeda, tapi lower(email) sama persis: ditolak
INSERT INTO pengguna VALUES (2, '[email protected]');

-- 11:00-14:00 menabrak shift pagi (sampai 12:00) dokter yang sama
INSERT INTO jadwal_dokter (dokter_id, poli, shift) VALUES
    (1, 'P-101', tstzrange('2026-03-02 11:00+07', '2026-03-02 14:00+07', '[)'));

-- EXCLUDE di atas btree: keunikan atas hasil ekspresi
CREATE TABLE pengguna (
    id    integer PRIMARY KEY,
    email text NOT NULL,
    EXCLUDE USING btree (lower(email) WITH =)
);

INSERT INTO pengguna VALUES (1, '[email protected]');

SELECT id, email, lower(email) AS email_baku
FROM pengguna
ORDER BY id;