Composite Key

Identity column memberi setiap tabel satu angka identitas buatan yang rapi. Namun sebagian entitas justru punya identitas alami yang terdiri dari lebih dari satu kolom: sebuah baris detail pesanan baru berarti jika kita tahu pesanan mana dan produk mana yang dipasangkan; harga grosir baru unik untuk kombinasi produk dan kota. Memaksa satu angka pengganti (id) pada tabel seperti itu tetap boleh, tetapi jaminan keunikan pasangan yang sebenarnya harus tetap dinyatakan — kalau tidak, tabel bisa menyimpan dua baris untuk pesanan 1 produk 10 tanpa ada yang melarang. PostgreSQL mendukung kunci gabungan (composite key) di PRIMARY KEY, UNIQUE, dan FOREIGN KEY. Pada chapter ini kita membangun tabel detail pesanan berkunci gabungan, membuktikan bahwa keunikannya berlaku untuk pasangan dan bukan per kolom, lalu menyambungkannya ke tabel lain lewat foreign key gabungan.

Primary Key dari Dua Kolom #

Deklarasi kunci gabungan tidak menempel pada satu kolom, melainkan ditulis sebagai daftar kolom di bagian akhir CREATE TABLE. Tabel detail_pesanan memakai pasangan pesanan_id dan produk_id sebagai identitas barisnya:

-- Kunci primer gabungan: identitas baris adalah PASANGAN kolom
CREATE TABLE detail_pesanan (
    pesanan_id   integer NOT NULL,
    produk_id    integer NOT NULL,
    jumlah       integer NOT NULL CHECK (jumlah > 0),
    harga_satuan numeric(12, 2) NOT NULL,
    PRIMARY KEY (pesanan_id, produk_id)
);

-- pesanan_id sama boleh berulang, produk_id sama juga boleh berulang
INSERT INTO detail_pesanan VALUES
    (1, 10, 2, 4500.00),
    (1, 11, 1, 30000.00),
    (2, 10, 1, 4500.00);

SELECT pesanan_id, produk_id, jumlah, harga_satuan
FROM detail_pesanan
ORDER BY pesanan_id, produk_id;

\d detail_pesanan

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 3
 pesanan_id | produk_id | jumlah | harga_satuan 
------------+-----------+--------+--------------
          1 |        10 |      2 |      4500.00
          1 |        11 |      1 |     30000.00
          2 |        10 |      1 |      4500.00
(3 rows)

                 Table "public.detail_pesanan"
    Column    |     Type      | Collation | Nullable | Default 
--------------+---------------+-----------+----------+---------
 pesanan_id   | integer       |           | not null | 
 produk_id    | integer       |           | not null | 
 jumlah       | integer       |           | not null | 
 harga_satuan | numeric(12,2) |           | not null | 
Indexes:
    "detail_pesanan_pkey" PRIMARY KEY, btree (pesanan_id, produk_id)
Check constraints:
    "detail_pesanan_jumlah_check" CHECK (jumlah > 0)

Data yang tersimpan memperlihatkan sifat terpenting kunci gabungan. Baris pertama dan kedua sama-sama bernilai pesanan_id = 1, dan baris pertama serta ketiga sama-sama bernilai produk_id = 10 — keduanya lolos, karena masing-masing pasangannya berbeda. Keunikan berlaku pada kombinasi (pesanan_id, produk_id), bukan pada tiap kolom secara terpisah. Dua detail lain dari hasil di atas layak dicatat: seluruh kolom kunci otomatis menjadi NOT NULL (terlihat pada kolom Nullable di \d), dan index detail_pesanan_pkey dibuat sebagai satu index btree di atas kedua kolom sekaligus, urutan (pesanan_id, produk_id) — urutan ini kelak memengaruhi query mana yang bisa memanfaatkan index secara efisien.

Batasan itu bekerja persis seperti primary key satu kolom: begitu satu pasangan lengkap sudah ada, pasangan yang sama ditolak:

-- Pasangan yang persis sama ditolak primary key
INSERT INTO detail_pesanan VALUES (1, 10, 5, 4500.00);

Output yang Dihasilkan:

psql:duplikat.sql:2: ERROR:  duplicate key value violates unique constraint "detail_pesanan_pkey"
DETAIL:  Key (pesanan_id, produk_id)=(1, 10) already exists.

Pesan errornya menyebut pasangan kunci yang bentrok secara utuh — Key (pesanan_id, produk_id)=(1, 10) already exists — bukan salah satu kolomnya. Inilah jaminan yang kita cari: tabel detail pesanan secara struktural tidak mungkin menyimpan produk yang sama dua kali dalam satu pesanan, tanpa bergantung pada disiplin kode aplikasi.

Foreign Key Gabungan #

Tabel lain bisa merujuk kunci gabungan dengan syarat yang sama seperti foreign key biasa: kolom yang dirujuk haruslah PRIMARY KEY atau UNIQUE, dan sekarang jumlah serta urutan kolomnya harus cocok. Kita buat tabel harga_grosir yang keunikan alaminya adalah pasangan produk dan kota, lalu tabel pesanan_kota yang merujuknya:

-- FK komposit: dua kolom bersama-sama merujuk UNIQUE dua kolom di tabel lain
CREATE TABLE harga_grosir (
    produk_id integer NOT NULL,
    kota_id   integer NOT NULL,
    harga     numeric(12, 2) NOT NULL,
    UNIQUE (produk_id, kota_id)
);

INSERT INTO harga_grosir VALUES
    (10, 1, 4000.00),
    (11, 2, 29000.00);

CREATE TABLE pesanan_kota (
    pesanan_id integer PRIMARY KEY,
    produk_id  integer NOT NULL,
    kota_id    integer NOT NULL,
    FOREIGN KEY (produk_id, kota_id)
        REFERENCES harga_grosir (produk_id, kota_id)
);

-- Pasangan (10, 1) ada di harga_grosir, jadi diterima
INSERT INTO pesanan_kota VALUES (1, 10, 1);

SELECT pesanan_id, produk_id, kota_id
FROM pesanan_kota
ORDER BY pesanan_id;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 1
 pesanan_id | produk_id | kota_id 
------------+-----------+---------
          1 |        10 |       1
(1 row)

Foreign key (produk_id, kota_id) REFERENCES harga_grosir (produk_id, kota_id) memasangkan kolom-kolomnya menurut posisi: produk_id di tabel anak diikat ke produk_id di tabel rujukan, dan kota_id ke kota_id. Kolom-kolom yang dirujuk tidak harus menjadi primary key — UNIQUE gabungan juga sah sebagai target, seperti pada contoh ini. Server memeriksa bahwa pasangan nilai yang dimasukkan benar-benar ada sebagai satu baris di tabel rujukan:

-- Pasangan (10, 9) tidak ada di harga_grosir
INSERT INTO pesanan_kota VALUES (2, 10, 9);

Output yang Dihasilkan:

psql:fk-langgar.sql:2: ERROR:  insert or update on table "pesanan_kota" violates foreign key constraint "pesanan_kota_produk_id_kota_id_fkey"
DETAIL:  Key (produk_id, kota_id)=(10, 9) is not present in table "harga_grosir".

Meskipun produk 10 ada di harga_grosir dan kota 9 mungkin saja kode kota yang sah di sistem lain, pasangan (10, 9) tidak pernah terdaftar sebagai satu baris harga — dan itulah satu-satunya hal yang diperiksa. Catatan penting soal urutan: karena pencocokan dilakukan per posisi, menukar kolom rujukan menjadi REFERENCES harga_grosir (kota_id, produk_id) tetap sah secara tipe (keduanya integer) dan tidak menghasilkan error — padahal maknanya sudah salah. Urutan kolom pada foreign key gabungan adalah tanggung jawab kita, bukan pemeriksaan server.

Query pada Sebagian Kunci #

Index kunci gabungan tersusun per kolom secara berurutan, dan hal itu memengaruhi dua hal sekaligus: query yang memakai kolom pertama kunci dapat memanfaatkan index, dan pencarian dengan hanya sebagian kunci memang sah — tetapi hasilnya bisa banyak baris:

-- Kunci parsial: satu kolom saja tidak menunjuk satu baris
SELECT pesanan_id, produk_id, jumlah
FROM detail_pesanan
WHERE pesanan_id = 1
ORDER BY produk_id;

-- Perbandingan baris-per-baris: pasangan lengkap menunjuk tepat satu baris
SELECT pesanan_id, produk_id, jumlah
FROM detail_pesanan
WHERE (pesanan_id, produk_id) = (1, 10);

Output yang Dihasilkan:

 pesanan_id | produk_id | jumlah 
------------+-----------+--------
          1 |        10 |      2
          1 |        11 |      1
(2 rows)

 pesanan_id | produk_id | jumlah 
------------+-----------+--------
          1 |        10 |      2
(1 row)

Query pertama menyaring dengan pesanan_id = 1 dan mengembalikan dua baris — pesanan 1 memang berisi dua produk. Itu bukan keanehan, melainkan konsekuensi logis dari “unik bersama, tidak sendiri-sendiri”. Query kedua memakai perbandingan baris-per-baris (pesanan_id, produk_id) = (1, 10) untuk menyebut pasangan kunci secara lengkap, dan tepat satu baris yang kembali. Kebiasaan yang layak dijaga: saat menampilkan atau mengubah satu baris berkunci gabungan, sebutkan seluruh kolom kuncinya di WHERE — sama seperti kita menyebut satu angka id pada tabel berkunci tunggal.

Kapan Composite, Kapan Surrogate #

Pilihan antara kunci gabungan dan angka pengganti (surrogate key) bukan soal benar-salah, melainkan kesesuaian dengan wujud data dan pola aksesnya:

Pola terakhir pada tabel di atas patut digarisbawahi: memakai identity column sebagai primary key tidak menutup pintu untuk tetap memasang UNIQUE gabungan. Kombinasi “surrogate untuk rujukan, composite unique untuk aturan bisnis” adalah desain yang sering dipilih di tabel-tabel besar dan sibuk. Satu perbedaan perilaku terakhir yang perlu diketahui: pada UNIQUE gabungan, baris yang memiliki NULL di salah satu kolom kuncinya tidak dianggap duplikat (perilaku NULL yang istimewa ini sudah kita bahas di chapter Unique Constraint), sedangkan PRIMARY KEY menuntut kedua kolomnya terisi.

Kunci gabungan mengunci keunikan nilai yang sama. Namun beberapa aturan bisnis tidak bisa dinyatakan sebagai kesamaan nilai: dua shift dokter yang sama tidak boleh tumpang tindih waktunya, dan “tumpang tindih” bukan operator =. Untuk aturan berbentuk relasi antar baris seperti itu, PostgreSQL menyediakan exclusion constraint — topik chapter berikutnya.

Referensi #

• Constraints pada CREATE TABLE
• CREATE TABLE Reference
• ALTER TABLE Reference

-- Kunci primer gabungan: identitas baris adalah PASANGAN kolom
CREATE TABLE detail_pesanan (
    pesanan_id   integer NOT NULL,
    produk_id    integer NOT NULL,
    jumlah       integer NOT NULL CHECK (jumlah > 0),
    harga_satuan numeric(12, 2) NOT NULL,
    PRIMARY KEY (pesanan_id, produk_id)
);

-- pesanan_id sama boleh berulang, produk_id sama juga boleh berulang
INSERT INTO detail_pesanan VALUES
    (1, 10, 2, 4500.00),
    (1, 11, 1, 30000.00),
    (2, 10, 1, 4500.00);

SELECT pesanan_id, produk_id, jumlah, harga_satuan
FROM detail_pesanan
ORDER BY pesanan_id, produk_id;

\d detail_pesanan

-- Pasangan yang persis sama ditolak primary key
INSERT INTO detail_pesanan VALUES (1, 10, 5, 4500.00);

-- FK komposit: dua kolom bersama-sama merujuk UNIQUE dua kolom di tabel lain
CREATE TABLE harga_grosir (
    produk_id integer NOT NULL,
    kota_id   integer NOT NULL,
    harga     numeric(12, 2) NOT NULL,
    UNIQUE (produk_id, kota_id)
);

INSERT INTO harga_grosir VALUES
    (10, 1, 4000.00),
    (11, 2, 29000.00);

CREATE TABLE pesanan_kota (
    pesanan_id integer PRIMARY KEY,
    produk_id  integer NOT NULL,
    kota_id    integer NOT NULL,
    FOREIGN KEY (produk_id, kota_id)
        REFERENCES harga_grosir (produk_id, kota_id)
);

-- Pasangan (10, 1) ada di harga_grosir, jadi diterima
INSERT INTO pesanan_kota VALUES (1, 10, 1);

SELECT pesanan_id, produk_id, kota_id
FROM pesanan_kota
ORDER BY pesanan_id;

-- Pasangan (10, 9) tidak ada di harga_grosir
INSERT INTO pesanan_kota VALUES (2, 10, 9);

-- Kunci parsial: satu kolom saja tidak menunjuk satu baris
SELECT pesanan_id, produk_id, jumlah
FROM detail_pesanan
WHERE pesanan_id = 1
ORDER BY produk_id;

-- Perbandingan baris-per-baris: pasangan lengkap menunjuk tepat satu baris
SELECT pesanan_id, produk_id, jumlah
FROM detail_pesanan
WHERE (pesanan_id, produk_id) = (1, 10);