COMPOSITE KEY

Relasi banyak-ke-banyak (many-to-many) tidak bisa disimpan di satu tabel biasa: satu pesanan memuat banyak produk, dan satu produk muncul di banyak pesanan. Solusinya adalah tabel junction yang merinci setiap pasangan — dan di tabel semacam itu, identitas baris bukan satu kolom melainkan gabungan dua kolom: baris “pesanan 1 produk 10” hanya boleh ada satu kali. Primary key yang dibangun dari lebih dari satu kolom disebut composite key. Pada chapter ini kita akan membangun tabel junction item_pesanan dengan PRIMARY KEY (pesanan_id, produk_id), menguji keunikan pasangannya, membuktikan bahwa kecocokan sebagian tetap sah, lalu membandingkan kapan composite key unggul dibanding surrogate key.

Tabel Junction untuk Relasi Many-to-Many #

Kita siapkan tiga tabel: dua tabel utama dan satu tabel junction di antaranya. Tabel utama memakai pola standar — primary key sintetis dengan AUTO_INCREMENT masih bisa dipakai; di sini cukup INT PRIMARY KEY agar fokus tetap pada kuncinya:

CREATE DATABASE belajar_komposit;
USE belajar_komposit;

Output yang Dihasilkan:

Query OK, 1 row affected (0.00 sec)

Database changed

CREATE TABLE pesanan (
  id INT PRIMARY KEY,
  pelanggan VARCHAR(100) NOT NULL
);

CREATE TABLE produk (
  id INT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  harga DECIMAL(10,2) NOT NULL
);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Tabel junction-nya menyimpan baris per pasangan pesanan-produk — lengkap dengan jumlah dan harga saat transaksi:

CREATE TABLE item_pesanan (
  pesanan_id INT NOT NULL,
  produk_id INT NOT NULL,
  jumlah INT NOT NULL,
  harga_satuan DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (pesanan_id, produk_id),
  FOREIGN KEY (pesanan_id) REFERENCES pesanan (id),
  FOREIGN KEY (produk_id) REFERENCES produk (id)
);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Mari kita bedah baris-baris pembentuk kuncinya:

1. Baris 2 dan 3 (pesanan_id INT NOT NULL, produk_id INT NOT NULL): Kedua kolom rujukan dideklarasikan eksplisit NOT NULL. Primary key sebenarnya memaksa keduanya wajib terisi, tetapi menuliskannya membuat niat desain langsung terbaca.
2. Baris 5 (PRIMARY KEY (pesanan_id, produk_id)): Bentuk table-level primary key dengan dua kolom di dalam tanda kurung. Keunikan berlaku pada gabungan nilainya — pasangan (1, 10) hanya boleh muncul sekali.
3. Baris 6-7 (FOREIGN KEY ... REFERENCES ...): Kedua kolom tetap dijaga foreign key ke tabel masing-masing, kombinasi yang menjadikan tabel junction sekaligus jembatan dan penjaga relasi.

Keunikan Pasangan #

Kita isi data: dua pesanan dari pelanggan berbeda dan dua produk. Perhatikan bahwa produk yang sama boleh hadir di kedua pesanan:

INSERT INTO pesanan (id, pelanggan) VALUES
  (1, 'Budi'),
  (2, 'Siti');

INSERT INTO produk (id, nama, harga) VALUES
  (10, 'Buku Tulis 58', 4500.00),
  (11, 'Pensil Kayu 2B', 2500.00);

INSERT INTO item_pesanan (pesanan_id, produk_id, jumlah, harga_satuan) VALUES
  (1, 10, 2, 4500.00),
  (1, 11, 3, 2500.00),
  (2, 10, 1, 4500.00);

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

Output yang Dihasilkan:

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

+------------+-----------+--------+--------------+
| pesanan_id | produk_id | jumlah | harga_satuan |
+------------+-----------+--------+--------------+
|          1 |        10 |      2 |      4500.00 |
|          1 |        11 |      3 |      2500.00 |
|          2 |        10 |      1 |      4500.00 |
+------------+-----------+--------+--------------+
3 rows in set (0.00 sec)

Tiga baris tersimpan. Baris (1, 10) dan baris (2, 10) sama-sama memuat produk 10 — dan keduanya sah, karena pasangannya berbeda. Inilah inti composite key: server hanya menolak pasangan yang persis sama. Kita buktikan dengan mencoba mengulang pasangan (1, 10):

-- @error
INSERT INTO item_pesanan (pesanan_id, produk_id, jumlah, harga_satuan) VALUES (1, 10, 5, 4500.00);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1062 (23000): Duplicate entry '1-10' for key 'item_pesanan.PRIMARY'
Bye

Server menolaknya dengan galat ERROR 1062 (23000): Duplicate entry '1-10' for key 'item_pesanan.PRIMARY' — nilai duplikat ditampilkan dalam format nilai1-nilai2, mencerminkan gabungan kedua kolom kunci. Pesanan yang ingin menambah jumlah produk yang sama harusnya mengubah baris yang ada lewat UPDATE, bukan menambah baris baru.

Sebaliknya, kecocokan sebagian tidak pernah melanggar apapun. Statement berikut memakai produk yang sudah dipakai pesanan 1, tetapi dengan nomor pesanan berbeda:

INSERT INTO item_pesanan (pesanan_id, produk_id, jumlah, harga_satuan) VALUES (2, 11, 2, 2500.00);

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

Output yang Dihasilkan:

Query OK, 1 row affected (0.00 sec)

+------------+-----------+--------+--------------+
| pesanan_id | produk_id | jumlah | harga_satuan |
+------------+-----------+--------+--------------+
|          1 |        10 |      2 |      4500.00 |
|          1 |        11 |      3 |      2500.00 |
|          2 |        10 |      1 |      4500.00 |
|          2 |        11 |      2 |      2500.00 |
+------------+-----------+--------+--------------+
4 rows in set (0.00 sec)

Baris (2, 11) diterima mulus — kolom produk_id boleh berulang, kolom pesanan_id boleh berulang, yang dilarang hanya keduanya berulang bersamaan. Struktur kolom kunci dapat dipastikan lewat DESC:

DESC item_pesanan;

Output yang Dihasilkan:

+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| pesanan_id   | int           | NO   | PRI | NULL    |       |
| produk_id    | int           | NO   | PRI | NULL    |       |
| jumlah       | int           | NO   |     | NULL    |       |
| harga_satuan | decimal(10,2) | NO   |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Kolom Key menampilkan PRI pada dua baris sekaligus — keduanya adalah satu kesatuan primary key, bukan dua primary key terpisah.

Kapan Composite, Kapan Surrogate #

Composite key bukan satu-satunya cara mendesain tabel junction. Alternatifnya adalah surrogate: kolom id AUTO_INCREMENT PRIMARY KEY ditambah UNIQUE (pesanan_id, produk_id) untuk menjaga keunikan pasangan. Keduanya sama-sama benar secara fungsi — pilihannya soal kebiasaan dan ukuran:

Composite key unggul saat tabel junction murni menjadi jembatan tanpa tabel lain yang merujuknya kembali. Surrogate unggul begitu tabel turunan perlu merujuk baris junction dengan satu kolom, atau saat framework aplikasi mengharuskan primary key satu kolom. Yang tidak boleh dikompromikan adalah keunikan pasangan — pasti dijaga oleh salah satu dari dua pola tersebut.

Referensi #

• CREATE TABLE Statement — MySQL 8.4 Reference Manual
• Constraints — MySQL 8.4 Reference Manual
• Multiple-Column Indexes — MySQL 8.4 Reference Manual

Tabel junction item_pesanan kini menjaga setiap pasangan pesanan-produk tetap tunggal, dengan relasi yang sah ke kedua tabel induknya. Semua constraint yang kita pasang sepanjang section ini — dari primary key sampai composite key — dipilih saat tabel dibuat; tetapi tabel dunia nyata terus berubah. Bagaimana menambah kolom, mengubah tipe, atau memasang constraint pada tabel yang sudah berisi data? Chapter Mengubah Struktur Tabel menutup section ini dengan ALTER TABLE. Sebelum itu, kita bereskan database latihan:

DROP DATABASE IF EXISTS belajar_komposit;

Output yang Dihasilkan:

Query OK, 3 rows affected (0.00 sec)

Tiga tabel — pesanan, produk, dan item_pesanan — ikut terbuang.