UNIQUE INDEX

Index pada chapter sebelumnya mempercepat pencarian, tetapi sama sekali tidak peduli isi datanya — nilai kembar diindex dengan senang hati. Banyak kolom dunia nyata menuntut lebih dari itu: satu alamat email hanya boleh dimiliki satu akun, satu kode voucher hanya boleh dipakai sekali, dan satu nomor kamar hanya boleh dipesan sekali per tanggal. CREATE UNIQUE INDEX menggabungkan dua kekuatan dalam satu struktur: kecepatan pencarian index biasa, plus jaminan server bahwa nilai kuncinya tidak pernah kembar. Pada chapter ini kita menguji penolakan duplikat lewat error 1062, membuktikan ulang kebiasaan NULL yang boleh ganda, membandingkan unique index dengan primary key, lalu menutup dengan unique index gabungan dua kolom.

Menyiapkan Tabel Latihan #

Tabel peserta mewakili kasus klasik penerapan unique index. Kolom email wajib diisi dan harus tunggal; kolom telepon opsional karena peserta boleh mendaftar tanpa nomor telepon — perbedaan yang akan menjadi bahan uji perilaku NULL:

sql
DROP DATABASE IF EXISTS belajar_index;
CREATE DATABASE belajar_index;
USE belajar_index;

Output yang Dihasilkan:

text
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

sql
CREATE TABLE peserta (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  telepon VARCHAR(20) NULL
);

INSERT INTO peserta (email, telepon) VALUES
  ('[email protected]', NULL),
  ('[email protected]', NULL);

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

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

CREATE UNIQUE INDEX dan Penolakan Duplikat #

Index unik pada email kita pasang sekarang, sebelum data kembar sempat masuk:

sql
CREATE UNIQUE INDEX idx_email ON peserta(email);

Output yang Dihasilkan:

text

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

Sintaksnya identik dengan CREATE INDEX biasa, hanya ditambah kata UNIQUE — namun maknanya berubah total. Setiap penyisipan dan pembaruan kini diperiksa server terhadap struktur index: bila nilai email sudah tercatat, statement ditolak sebelum menyentuh tabel. Mari kita buktikan dengan sengaja mendaftarkan pendaftar kedua memakai email milik Budi:

sql
-- @error
INSERT INTO peserta (email, telepon) VALUES ('[email protected]', '0812-1111-2222');

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'peserta.idx_email'
Bye

Server menolak dengan ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'peserta.idx_email' — pesan yang sekaligus menampilkan nilai duplikatnya dan nama index yang menolak. Statement dibatalkan utuh, sehingga baris ketiga tidak pernah tercipta. Pendaftar baru dengan email yang benar-benar baru tetap diterima seperti biasa:

sql
INSERT INTO peserta (email, telepon) VALUES ('[email protected]', '0811-3333-4444');

SELECT id, email, telepon FROM peserta ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 1 row affected (0.00 sec)

+----+------------------+----------------+
| id | email            | telepon        |
+----+------------------+----------------+
|  1 | [email protected] | NULL           |
|  2 | [email protected] | NULL           |
|  4 | [email protected]  | 0811-3333-4444 |
+----+------------------+----------------+
3 rows in set (0.00 sec)

Tiga peserta tercatat, tetapi perhatikan detail yang sering mengejutkan: baris Ani mendapat id 4, bukan 3. Penyisipan yang ditolak tadi tetap mencadangkan satu nilai auto-increment di InnoDB — nilai 3 hangus tanpa pernah dipakai, sehingga deretan id kini melompat. Perilaku tolak-atau-terima penuh pada datanya tetap terjamin (baris duplikat tidak pernah tercipta); hanya pembilang identitasnya yang maju lebih dulu. Kebiasaan “lubang” pada auto-increment seperti ini normal di InnoDB dan tidak perlu diperbaiki.

Data Duplikat Lama Memblokir Pembuatan Index #

Aturan keunikan tidak hanya berlaku ke depan — ia juga diberlakukan ke belakang saat index dibuat. Bila tabel sudah menampung nilai kembar, CREATE UNIQUE INDEX menolak berdiri sama sekali:

sql
CREATE TABLE pelanggan_lama (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) NOT NULL
);

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

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

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

sql
-- @error
CREATE UNIQUE INDEX idx_email_lama ON pelanggan_lama(email);

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'pelanggan_lama.idx_email_lama'
Bye

Error 1062 yang sama muncul, kali ini pada statement DDL: MySQL memeriksa seluruh isi kolom sebelum membangun struktur index, dan menemukan [email protected] dua kali. Urutan kerja yang benar untuk tabel lama seperti ini adalah membersihkan duplikat terlebih dahulu — menggabungkan baris, menghapus yang ganda, atau memutuskan kolom mana yang benar-benar unik — baru kemudian memasang indexnya. Mencoba memaksakan index sambil berharap server “menoleransi” data lama akan selalu berakhir di penolakan ini.

NULL pada Unique Index Tetap Boleh Ganda #

Perilaku yang paling sering mengejutkan: unique index tidak menganggap NULL sebagai nilai yang perlu dijadikan unik. Dua nilai NULL tidak dianggap sama satu sama lain, sehingga kolom unik yang boleh kosong tetap menerima banyak baris NULL. Kita buktikan pada kolom telepon:

sql
CREATE UNIQUE INDEX idx_telepon ON peserta(telepon);

INSERT INTO peserta (email, telepon) VALUES
  ('[email protected]', NULL),
  ('[email protected]', NULL);

SELECT id, email, telepon FROM peserta ORDER BY id;

Output yang Dihasilkan:

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

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

+----+-------------------+----------------+
| id | email             | telepon        |
+----+-------------------+----------------+
|  1 | [email protected]  | NULL           |
|  2 | [email protected]  | NULL           |
|  4 | [email protected]   | 0811-3333-4444 |
|  5 | [email protected]  | NULL           |
|  6 | [email protected] | NULL           |
+----+-------------------+----------------+
5 rows in set (0.00 sec)

Kedua penyisipan berhasil — kolom telepon kini memuat empat baris NULL (Siti, Joko, Rizky, dan baris pertama Budi) tanpa pelanggaran keunikan, sementara dua nilai telepon yang terisi tetap wajib tidak kembar. Perilaku ini persis dengan yang kita buktikan pada chapter UNIQUE Constraint di section table-design, karena keduanya dijamin struktur yang sama: constraint UNIQUE selalu diimplementasikan sebagai unique index. Konsekuensi praktisnya juga sama — bila kolom unik tidak boleh kosong ganda, kombinasikan dengan NOT NULL seperti pada kolom email kita, atau pertimbangkan menjadikannya primary key.

UNIQUE Index dan PRIMARY KEY #

Keduanya sama-sama menjamin keunikan lewat index, tetapi membidik kebutuhan yang berbeda:

Aspek	PRIMARY KEY	UNIQUE index
Jumlah per tabel	Maksimal satu	Boleh banyak kolom/kombinasi
Nilai `NULL`	Dilarang	Boleh, dan boleh lebih dari satu baris
Tujuan utama	Identitas teknis baris	Integritas nilai bisnis (email, kode, kombinasi)
Cara pembuatan	Klausa `PRIMARY KEY`	`CREATE UNIQUE INDEX` atau klausa `UNIQUE`

Kolom id pada peserta dan index idx_email pada kolom email memperagakan pembagian kerja ini: id adalah identitas baris yang impersonal, sedangkan email adalah aturan bisnis yang bisa saja berubah tanpa mengubah identitas pesertanya. Bila suatu hari sistem berubah dan email boleh kembar — misalnya satu email dipakai keluarga — unique index email bisa dijatuhkan tanpa menyentuh primary key.

Lookup pada Unique Index Lebih Cepat Lagi #

Selain integritas, unique index memberi tahu optimizer sebuah fakta berharga: pencarian equality pada kunci unik menghasilkan maksimal satu baris. MySQL memperlakukan kasus ini sebagai tipe akses khusus bernama const:

sql
EXPLAIN SELECT * FROM peserta WHERE email = '[email protected]';

Output yang Dihasilkan:

text
+----+-------------+---------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | peserta | NULL       | const | idx_email     | idx_email | 402     | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Perhatikan kolom type bernilai const dan key bernilai idx_email — server menuju langsung ke satu-satunya baris mungkin lewat index, tanpa memperkirakan rentang baris. Pada index biasa tipe aksesnya ref karena server harus siap menemukan beberapa baris. Pembacaan mendalam kolom-kolom EXPLAIN akan kita lakukan di chapter Cara MySQL Menggunakan INDEX; untuk sekarang, cukup pegang simpulannya: unique index bukan cuma penjaga integritas, tetapi juga jalur pencarian paling cepat untuk lookup tunggal.

Unique Index Gabungan Dua Kolom #

Keunikan sering berlaku untuk kombinasi kolom, bukan kolom tunggal: satu ruang rapat hanya boleh dipakai sekali per hari, dan satu pasangan nilai itulah yang harus unik. Kita buat tabel jadwal dengan tiga pemesanan awal:

sql
CREATE TABLE jadwal (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ruang VARCHAR(20) NOT NULL,
  hari VARCHAR(10) NOT NULL
);

INSERT INTO jadwal (ruang, hari) VALUES
  ('R1', 'Senin'),
  ('R2', 'Senin'),
  ('R1', 'Selasa');

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

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

sql
CREATE UNIQUE INDEX idx_ruang_hari ON jadwal(ruang, hari);

Output yang Dihasilkan:

text

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

Index idx_ruang_hari menjaga pasangan (ruang, hari) secara bersama. Nilai R1 boleh muncul berkali-kali dan Senin boleh muncul berkali-kali — yang tidak boleh adalah kombinasi yang sama dua kali. Penyisipan ruang yang sama di hari berbeda diterima, lalu penyisipan kombinasi yang sudah ada ditolak:

sql
INSERT INTO jadwal (ruang, hari) VALUES ('R2', 'Selasa');

Output yang Dihasilkan:

text

Query OK, 1 row affected (0.00 sec)

sql
-- @error
INSERT INTO jadwal (ruang, hari) VALUES ('R1', 'Senin');

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

ERROR 1062 (23000): Duplicate entry 'R1-Senin' for key 'jadwal.idx_ruang_hari'
Bye

Baris ('R2', 'Selasa') masuk tanpa masalah meskipun R2 dan Selasa masing-masing sudah pernah dipakai. Statement kedua ditolak dengan ERROR 1062, dan pesannya menampilkan nilai gabungan 'R1-Senin' — kedua nilai kunci digabung dengan tanda hubung supaya jelas kombinasi mana yang bentrok. Struktur multi kolom seperti ini memperkenalkan pula aturan urutan kolom yang berdampak besar pada query non-unik — materi inti chapter composite index berikutnya.

Membersihkan Database Latihan #

Database belajar_index kita jatuhkan kembali:

sql
DROP DATABASE belajar_index;

Output yang Dihasilkan:

text

Query OK, 3 rows affected (0.00 sec)

Referensi #

Unique index kini utuh kita kuasai: tolak duplikat lewat ERROR 1062, audit data lama sebelum pemasangan, perlakuan NULL yang tetap bebas ganda, posisinya di samping primary key, hingga keunikan kombinasi dua kolom. Bab berikutnya melanjutkan tema multi kolom ke sisi performanya — composite index — di mana urutan kolom menentukan query mana yang bisa dilayani index dan query mana yang harus kembali ke full table scan.