Lima chapter pertama memperlakukan index sebagai alat percepatan: dibuat demi query yang lebih cepat, dijatuhkan bila tak lagi terpakai. Index unik menambahkan wajah ketiga — index sebagai penjaga integritas. Satu kata UNIQUE mengubah index dari jalan pintas pembaca menjadi gerbang pemeriksaan yang tak bisa ditipu oleh INSERT sembarangan, dan gerbang itu bekerja dua arah sekaligus: duplikasi ditolak di jalur tulis, pencarian tetap cepat di jalur baca. Chapter ini membuktikan bahwa primary key dan constraint UNIQUE sebenarnya adalah index unik yang menyamar, lalu menguji tiga variasi khas PostgreSQL: keunikan atas ekspresi lower(email), perlakuan terhadap NULL, dan index unik parsial yang hanya menjaga sebagian baris.
Constraint UNIQUE dan Primary Key adalah Index #
Mari mulai dari fakta yang sering luput: constraint yang kita buat lewat PRIMARY KEY dan UNIQUE diimplementasikan sebagai index unik. Tabel pelanggan dan pengguna untuk chapter ini dibuat seperti biasa — dua tabel, 1000 baris pelanggan, lima baris pengguna dengan email yang sengaja campur huruf besar-kecil dan dua baris tanpa email:
-- Dua tabel chapter ini: pelanggan (domain utama section ini) dan
-- pengguna (kasus email campuran huruf besar-kecil dengan nilai NULL).
CREATE TABLE pelanggan (
id integer PRIMARY KEY,
nama text NOT NULL,
email text NOT NULL,
kota text NOT NULL,
kategori text NOT NULL,
saldo numeric(12,2) NOT NULL,
aktif boolean NOT NULL
);
INSERT INTO pelanggan (id, nama, email, kota, kategori, saldo, aktif)
SELECT
g,
'Pelanggan ' || lpad(g::text, 4, '0'),
'pelanggan' || lpad(g::text, 4, '0') || '@example.com',
(ARRAY['Jakarta','Bandung','Surabaya','Medan','Denpasar'])[1 + (g - 1) / 200],
(ARRAY['reguler','silver','gold'])[1 + g % 3],
((g - 1) * 1000 + 500)::numeric(12,2),
(g % 10 <> 0)
FROM generate_series(1, 1000) AS g;
ANALYZE pelanggan;
CREATE TABLE pengguna (
id integer PRIMARY KEY,
nama text NOT NULL,
email text
);
INSERT INTO pengguna (id, nama, email) VALUES
(1, 'Budi Santoso', '[email protected]'),
(2, 'Siti Rahayu', '[email protected]'),
(3, 'Dewi Lestari', '[email protected]'),
(4, 'Rina Melati', NULL),
(5, 'Ratna Putri', NULL);
ANALYZE pengguna;
-- Index bawaan: primary key pelanggan sudah otomatis punya index sendiri.
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'pelanggan'
ORDER BY indexname;Output yang Dihasilkan:
CREATE TABLE
INSERT 0 1000
ANALYZE
CREATE TABLE
INSERT 0 5
ANALYZE
indexname | indexdef
----------------+-------------------------------------------------------------------------
pelanggan_pkey | CREATE UNIQUE INDEX pelanggan_pkey ON public.pelanggan USING btree (id)
(1 row)Perhatikan definisi index pelanggan_pkey di output: CREATE UNIQUE INDEX ... USING btree (id). Tidak ada perintah CREATE INDEX yang pernah kita tulis untuknya — deklarasi PRIMARY KEY di CREATE TABLE yang memintanya, dan PostgreSQL menamainya dengan pola <tabel>_pkey. Constraint UNIQUE biasa membuat index kembar dengan pola nama <tabel>_<kolom>_key. Chapter Membuat dan Menghapus Index sudah menunjukkan sisi kepemilikannya: index penopang constraint tidak bisa di-DROP INDEX begitu saja. Sebagai gantinya, CREATE UNIQUE INDEX memungkinkan kita menegakkan keunikan dengan kendali penuh — nama sendiri, kolom sendiri, bahkan ekspresi dan syarat sendiri.
CREATE UNIQUE INDEX dan Penolakan Duplikasi #
Dua index unik berikut dipasang pada tabel pengguna: satu pada kolom email apa adanya, satu pada ekspresi lower(email):
-- Index unik biasa: menolak nilai email yang sama persis.
CREATE UNIQUE INDEX idx_pengguna_email ON pengguna (email);
-- Index unik atas ekspresi: menolak duplikasi SETELAH lower() diterapkan,
-- sehingga dua email yang sama walau beda kapitalisasi ikut tertangkap.
CREATE UNIQUE INDEX idx_pengguna_email_lower ON pengguna (lower(email));
-- Pencarian case-insensitive kini punya jalur index sendiri.
SELECT id, nama, email
FROM pengguna
WHERE lower(email) = '[email protected]';Output yang Dihasilkan:
CREATE INDEX
CREATE INDEX
id | nama | email
----+--------------+------------------
1 | Budi Santoso | [email protected]
(1 row)Tiga fakta lahir dari output itu. Pertama, kedua CREATE INDEX sukses — termasuk index unik pada tabel yang memuat dua baris ber-email NULL; arti peristiwa ini dibahas dua bagian di bawah. Kedua, query WHERE lower(email) = '[email protected]' menemukan baris Budi yang tersimpan sebagai [email protected] — index pada ekspresi membuat pencarian case-insensitive sama cepatnya dengan pencarian biasa, tanpa memaksa Anda mengubah data yang tersimpan. Ketiga, index unik atas ekspresi menegakkan keunikan hasil ekspresi, bukan kolom aslinya.
Sekarang saatnya menguji gerbangnya. Insert pertama mencoba email yang sama persis dengan milik Budi:
-- Email yang sama persis ditolak mentah-mentah oleh index unik.
INSERT INTO pengguna (id, nama, email) VALUES (6, 'Budi Kedua', '[email protected]');Output yang Dihasilkan:
psql:gagal-duplikat.sql:2: ERROR: duplicate key value violates unique constraint "idx_pengguna_email"
DETAIL: Key (email)=([email protected]) already exists.Bentuk pesannya patut dibaca per baris: ERROR: duplicate key value violates unique constraint "idx_pengguna_email" menyebut siapa penolaknya — index unik buatan kita sendiri, bukan constraint dari CREATE TABLE. Baris DETAIL: Key (email)=([email protected]) already exists. menyebut pasangan kunci dan nilai yang bentrok, sehingga baris mana yang bermasalah langsung teridentifikasi. Perintah ditolak sebelum satu baris pun tertulis; file ini memang dijalankan dengan ekspektasi error (exit code 3), itulah sebabnya blok outputnya berisi pesan error.
Insert kedua lebih licik — email sama, kapitalisasi beda:
-- '[email protected]' berbeda bagi index biasa, tetapi IDENTIK bagi
-- index pada lower(email) -- index unik ekspresi yang menolaknya.
INSERT INTO pengguna (id, nama, email) VALUES (6, 'Budi Kedua', '[email protected]');Output yang Dihasilkan:
psql:gagal-duplikat-case.sql:3: ERROR: duplicate key value violates unique constraint "idx_pengguna_email_lower"
DETAIL: Key (lower(email))=([email protected]) already exists.Bagi idx_pengguna_email, string '[email protected]' dan '[email protected]' adalah dua nilai berbeda — insert ini lolos dari index pertama. Tetapi idx_pengguna_email_lower membandingkan hasil lower() dari keduanya, dan hasilnya identik, sehingga DETAIL kini menyebut Key (lower(email))=([email protected]) already exists. Tanpa index ekspresi, duplikasi huruf kecil-besar akan tersimpan diam-diam di database — bug klasik aplikasi yang lupa menormalkan email. Index atas ekspresi sendiri punya satu syarat khusus yang membedakannya dari index kolom biasa — catatan berikut merinci syaratnya.
NULL Tidak Dianggap Duplikat — Secara Default #
Kenapa index unik pada email berhasil dibuat padahal tabel pengguna memuat dua baris ber-email NULL? Karena aturan default PostgreSQL berbunyi NULLS DISTINCT: dua NULL tidak dianggap sama satu sama lain. NULL bukan nilai — NULL adalah penanda “tidak ada nilai”, dan membandingkan dua ketiadaan tidak menghasilkan bentrok. Akibatnya, tabel boleh memuat baris ber-email NULL sebanyak apa pun di bawah index unik biasa.
PostgreSQL 15 menambahkan saklar untuk membalik aturan itu: NULLS NOT DISTINCT. Dengan saklar ini, NULL diperlakukan seperti nilai biasa yang sama dengan sesama NULL. Tabel pengguna kita punya dua baris NULL, jadi mencoba memasang index versi ini harusnya gagal — dan memang gagal:
-- NULLS NOT DISTINCT (PostgreSQL 15+): baris ber-email NULL ikut
-- diperlakukan sebagai duplikat satu sama lain. Tabel pengguna punya
-- dua NULL, sehingga pembuatan index ini ditolak.
CREATE UNIQUE INDEX idx_pengguna_email_nnd ON pengguna (email) NULLS NOT DISTINCT;Output yang Dihasilkan:
psql:gagal-null.sql:4: ERROR: could not create unique index "idx_pengguna_email_nnd"
DETAIL: Key (email)=(null) is duplicated.Pesan could not create unique index "idx_pengguna_email_nnd" diikuti DETAIL: Key (email)=(null) is duplicated. — pembuatannya ditolak karena data yang sudah ada melanggar aturan baru itu. Bila tabel Anda sehat (paling banyak satu NULL) dan index NULLS NOT DISTINCT berhasil dibuat, perintah INSERT kedua yang bernilai NULL pada kolom itu akan ditolak dengan pesan duplikat seperti biasa. Untuk kompatibilitas dengan PostgreSQL 14 dan lebih lama, tetap gunakan bentuk default dan cegah NULL berlebih dengan constraint NOT NULL.
Index Unik Parsial: Penjaga yang Hanya Berjaga di Sebagian Meja #
Puncak fleksibilitas index unik adalah varian parsial: keunikan hanya ditegakkan pada baris yang memenuhi sebuah kondisi WHERE. Skenario paling umum di dunia nyata: tabel memakai kolom penanda status (aktif/tidak aktif), dan aturan bisnis berbunyi “email tidak boleh dipakai dua akun aktif sekaligus — tapi akun lama yang sudah dinonaktifkan boleh tetap tersimpan, dan orangnya boleh mendaftar ulang”. Index unik penuh terlalu kaku untuk aturan itu; index parsial tepat sasaran:
-- Index unik parsial: keunikan hanya ditegakkan pada baris yang aktif.
CREATE UNIQUE INDEX idx_pelanggan_email_aktif ON pelanggan (email) WHERE aktif;
-- Pelanggan 10 tidak aktif, jadi emailnya boleh dipakai lagi oleh
-- baris lain yang juga tidak aktif.
INSERT INTO pelanggan (id, nama, email, kota, kategori, saldo, aktif)
VALUES (1001, 'Pelanggan 1001', '[email protected]', 'Jakarta', 'reguler', 1000500.00, false);
-- Bukti: dua baris kini ber-email sama, dan keduanya tidak aktif.
SELECT id, email, aktif
FROM pelanggan
WHERE email = '[email protected]'
ORDER BY id;Output yang Dihasilkan:
CREATE INDEX
INSERT 0 1
id | email | aktif
------+---------------------------+-------
10 | [email protected] | f
1001 | [email protected] | f
(2 rows)Output membuktikan aturan itu bekerja dua arah: INSERT pelanggan 1001 dengan email milik pelanggan 10 berhasil (INSERT 0 1), karena keduanya tidak aktif — baris di luar jangkauan WHERE aktif tidak dijaga index ini. Query penutup memperlihatkan dua baris dengan email yang sama persis hidup berdampingan secara legal. Sekarang uji sisi sebaliknya — email milik pelanggan yang aktif:
-- Email milik pelanggan AKTIF tidak boleh dipakai baris aktif lain:
-- index unik parsial menolak INSERT ini.
INSERT INTO pelanggan (id, nama, email, kota, kategori, saldo, aktif)
VALUES (1002, 'Pelanggan 1002', '[email protected]', 'Jakarta', 'reguler', 1001500.00, true);Output yang Dihasilkan:
psql:gagal-partial.sql:4: ERROR: duplicate key value violates unique constraint "idx_pelanggan_email_aktif"
DETAIL: Key (email)=([email protected]) already exists.Penolakan datang dari idx_pelanggan_email_aktif karena kedua baris — yang lama (pelanggan 42, aktif) dan yang baru (pelanggan 1002, aktif) — sama-sama berada dalam jangkauan index. Manfaat sampingannya ikut terasa: index parsial hanya menampung baris aktif (900 dari 1000 baris di tabel kita), sehingga lebih kecil dan lebih murah dipelihara daripada index penuh. Pola “satu aktif saja” ini juga menjadi dasar pola soft delete — akun lama diberi penanda non-aktif alih-alih dihapus, riwayatnya tetap utuh, dan emailnya bebas dipakai ulang.
Rangkuman tiga wajah index unik:
| Bentuk | Menegakkan | Catatan |
|---|---|---|
CREATE UNIQUE INDEX t (kolom) | Keunikan nilai kolom | NULL boleh berulang (default) |
... (lower(kolom)) | Keunikan hasil ekspresi | Ekspresi wajib immutable |
... WHERE kondisi | Keunikan hanya baris yang lolos kondisi | Index lebih kecil, aturan bisnis fleksibel |
PRIMARY KEY / UNIQUE di DDL | Keunikan lewat constraint | Membuat index <tabel>_pkey / _key otomatis |
Klausa WHERE aktif yang baru kita pakai sebenarnya adalah pintu ke teknik yang jauh lebih luas: index parsial biasa — tanpa keunikan — yang menyusut dari 1000 baris menjadi ratusan demi query yang lebih cepat dan penulisan yang lebih murah. Chapter Partial Index membuka pintu itu.
Referensi #
Berkas Sumber
Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.
buat-tabel.sql
Unduh berkas-- Dua tabel chapter ini: pelanggan (domain utama section ini) dan
-- pengguna (kasus email campuran huruf besar-kecil dengan nilai NULL).
CREATE TABLE pelanggan (
id integer PRIMARY KEY,
nama text NOT NULL,
email text NOT NULL,
kota text NOT NULL,
kategori text NOT NULL,
saldo numeric(12,2) NOT NULL,
aktif boolean NOT NULL
);
INSERT INTO pelanggan (id, nama, email, kota, kategori, saldo, aktif)
SELECT
g,
'Pelanggan ' || lpad(g::text, 4, '0'),
'pelanggan' || lpad(g::text, 4, '0') || '@example.com',
(ARRAY['Jakarta','Bandung','Surabaya','Medan','Denpasar'])[1 + (g - 1) / 200],
(ARRAY['reguler','silver','gold'])[1 + g % 3],
((g - 1) * 1000 + 500)::numeric(12,2),
(g % 10 <> 0)
FROM generate_series(1, 1000) AS g;
ANALYZE pelanggan;
CREATE TABLE pengguna (
id integer PRIMARY KEY,
nama text NOT NULL,
email text
);
INSERT INTO pengguna (id, nama, email) VALUES
(1, 'Budi Santoso', '[email protected]'),
(2, 'Siti Rahayu', '[email protected]'),
(3, 'Dewi Lestari', '[email protected]'),
(4, 'Rina Melati', NULL),
(5, 'Ratna Putri', NULL);
ANALYZE pengguna;
-- Index bawaan: primary key pelanggan sudah otomatis punya index sendiri.
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'pelanggan'
ORDER BY indexname;
buat-unique-index.sql
Unduh berkas-- Index unik biasa: menolak nilai email yang sama persis.
CREATE UNIQUE INDEX idx_pengguna_email ON pengguna (email);
-- Index unik atas ekspresi: menolak duplikasi SETELAH lower() diterapkan,
-- sehingga dua email yang sama walau beda kapitalisasi ikut tertangkap.
CREATE UNIQUE INDEX idx_pengguna_email_lower ON pengguna (lower(email));
-- Pencarian case-insensitive kini punya jalur index sendiri.
SELECT id, nama, email
FROM pengguna
WHERE lower(email) = '[email protected]';
gagal-duplikat-case.sql
Unduh berkas-- '[email protected]' berbeda bagi index biasa, tetapi IDENTIK bagi
-- index pada lower(email) -- index unik ekspresi yang menolaknya.
INSERT INTO pengguna (id, nama, email) VALUES (6, 'Budi Kedua', '[email protected]');
gagal-duplikat.sql
Unduh berkas-- Email yang sama persis ditolak mentah-mentah oleh index unik.
INSERT INTO pengguna (id, nama, email) VALUES (6, 'Budi Kedua', '[email protected]');
gagal-null.sql
Unduh berkas-- NULLS NOT DISTINCT (PostgreSQL 15+): baris ber-email NULL ikut
-- diperlakukan sebagai duplikat satu sama lain. Tabel pengguna punya
-- dua NULL, sehingga pembuatan index ini ditolak.
CREATE UNIQUE INDEX idx_pengguna_email_nnd ON pengguna (email) NULLS NOT DISTINCT;
gagal-partial.sql
Unduh berkas-- Email milik pelanggan AKTIF tidak boleh dipakai baris aktif lain:
-- index unik parsial menolak INSERT ini.
INSERT INTO pelanggan (id, nama, email, kota, kategori, saldo, aktif)
VALUES (1002, 'Pelanggan 1002', '[email protected]', 'Jakarta', 'reguler', 1001500.00, true);
partial-unik.sql
Unduh berkas-- Index unik parsial: keunikan hanya ditegakkan pada baris yang aktif.
CREATE UNIQUE INDEX idx_pelanggan_email_aktif ON pelanggan (email) WHERE aktif;
-- Pelanggan 10 tidak aktif, jadi emailnya boleh dipakai lagi oleh
-- baris lain yang juga tidak aktif.
INSERT INTO pelanggan (id, nama, email, kota, kategori, saldo, aktif)
VALUES (1001, 'Pelanggan 1001', '[email protected]', 'Jakarta', 'reguler', 1000500.00, false);
-- Bukti: dua baris kini ber-email sama, dan keduanya tidak aktif.
SELECT id, email, aktif
FROM pelanggan
WHERE email = '[email protected]'
ORDER BY id;