Kursus MariaDB / Mengenali Baris dengan PRIMARY KEY, AUTO_INCREMENT, dan UNIQUE
PRIMARY KEY mengenali setiap baris secara unik, sedangkan UNIQUE membatasi nilai lain atau gabungan nilai yang tidak boleh berulang. AUTO_INCREMENT membuat pengenal bilangan bulat; fitur ini tidak menggantikan deklarasi kunci dan tidak menjamin urutan tanpa nomor terlewat. Gunakan ketiganya sesuai identitas data dan aturan aplikasi.
Pisahkan pengenal baris dari aturan keunikan aplikasi
Dua pelanggan berbeda dapat memiliki nama sama, sehingga nama bukan pengenal baris yang andal. Contoh ini memakai nomor pelanggan stabil yang dibuat otomatis sebagai kunci primer dan memperlakukan keunikan surel sebagai aturan terpisah. Panduan struktur tabel menunjukkan tempat definisi tersebut. Gabungkan kunci dengan aturan keberadaan nilai serta CHECK sesuai kebutuhan data.
| Fitur | Peran | Kondisi penting |
|---|---|---|
| PRIMARY KEY | Mengenali baris | Satu definisi kunci primer per tabel; kolomnya tidak NULL dan gabungannya unik |
| UNIQUE | Menolak nilai atau gabungan kolom yang berulang | Beberapa definisi UNIQUE dimungkinkan; kolom UNIQUE yang mengizinkan NULL dapat berisi beberapa NULL |
| AUTO_INCREMENT | Membuat bilangan bulat ketika pengenal tidak dicantumkan | Satu kolom AUTO_INCREMENT per tabel; harus diindeks dan nomor dapat terlewat |
Satu kunci primer tidak selalu berarti satu kolom. Kunci primer gabungan adalah satu definisi yang melibatkan beberapa kolom. AUTO_INCREMENT tidak harus menjadi kunci primer dalam setiap rancangan, tetapi satu kunci primer bilangan bulat merupakan titik awal sederhana untuk contoh ini.
Jalankan contoh lengkap pelanggan dan kunci
Hubungkan sebagai course_user ke server latihan dengan shopcourse tersedia. Panduan klien dan berkas SQL menjelaskan koneksi serta SOURCE. Jalankan pemeriksaan berikut dalam koneksi, bukan di shell sistem operasi:
SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;
Basis data yang dipilih seharusnya shopcourse. Simpan contoh normal lengkap sebagai berkas UTF-8 berakhiran .sql, lalu jalankan dengan SOURCE serta jalur lokal sebenarnya. Pisahkan perintah yang sengaja gagal karena dapat menghentikan berkas. Gunakan instans belajar dan periksa nama tabel latihan yang sudah ada sebelum membuatnya.
customers_key_lab adalah tabel latihan terpisah untuk memahami pengenal sebelum memodelkan hubungan pelanggan dan pesanan. Contoh mengizinkan surel yang belum diketahui dengan membuat kolom tersebut nullable, yaitu boleh berisi NULL. Kolom kunci primer dideklarasikan NOT NULL secara eksplisit dan tidak dicantumkan pada INSERT pelanggan:
USE shopcourse;
CREATE TABLE customers_key_lab (
customer_id INT NOT NULL AUTO_INCREMENT,
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(254) NULL,
PRIMARY KEY (customer_id),
CONSTRAINT uq_customers_key_email UNIQUE (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO customers_key_lab (customer_name, email) VALUES
('Alex Chen', 'alex@example.com'),
('Sam Patel', NULL),
('Jordan Lee', NULL);
SELECT customer_id, customer_name, email FROM customers_key_lab ORDER BY customer_id;
CREATE TABLE product_codes_key_lab (
supplier_id INT NOT NULL,
supplier_code VARCHAR(30) NOT NULL,
label VARCHAR(100) NOT NULL,
PRIMARY KEY (supplier_id, supplier_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO product_codes_key_lab VALUES
(1, 'N001', 'Notebook'),
(2, 'N001', 'Notebook from supplier two');
SELECT supplier_id, supplier_code, label FROM product_codes_key_lab ORDER BY supplier_id;
CREATE TABLE auto_gap_lab (
item_id INT PRIMARY KEY AUTO_INCREMENT,
label VARCHAR(30) NOT NULL
) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO auto_gap_lab (label) VALUES ('Rolled-back row');
ROLLBACK;
INSERT INTO auto_gap_lab (label) VALUES ('Saved row');
SELECT item_id, label FROM auto_gap_lab;
| customer_id | customer_name | |
|---|---|---|
| 1 | Alex Chen | alex@example.com |
| 2 | Sam Patel | NULL |
| 3 | Jordan Lee | NULL |
Pada tabel baru dengan pengaturan kenaikan dan offset bawaan 1, pengenal pelanggan adalah 1, 2, dan 3. UNIQUE(email) mengizinkan kedua surel NULL dan tidak mewajibkan surel diisi. Jika surel wajib, tambahkan kebijakan NOT NULL yang sesuai dan tinjau baris NULL lama sebelum menerapkannya pada tabel yang sudah ada. Panduan NULL dan batasan masukan menjelaskan perbedaannya.
Referensi AUTO_INCREMENT dan aturan PRIMARY KEY serta UNIQUE menjelaskan definisinya. Alamat example.com di sini hanya nilai contoh dalam basis data, bukan alamat tujuan pengiriman surel.
Uji surel dan nilai kunci primer duplikat
Setelah berkas normal selesai, jalankan dua kesalahan yang disengaja ini secara terpisah. Perintah pertama mengulang surel yang sudah ada. Perintah kedua memberikan pengenal pelanggan 1 secara eksplisit:
INSERT INTO customers_key_lab (customer_name, email) VALUES ('Another Alex', 'alex@example.com');
INSERT INTO customers_key_lab (customer_id, customer_name) VALUES (1, 'Duplicate ID');
Bandingkan nama batasan dalam pesan kesalahan. Sesudah masing-masing kegagalan, SELECT COUNT(*) FROM customers_key_lab harus tetap menghasilkan tiga. Jangan langsung beralih ke INSERT IGNORE atau REPLACE untuk menyembunyikan masalah. REPLACE dapat mengganti baris lama yang berkonflik dan bukan sekadar cara melewati duplikat tanpa dampak.
Kolasi kolom surel memengaruhi perbandingan duplikat. Dengan utf8mb4_unicode_ci, perubahan huruf besar dan kecil tetap dapat dianggap duplikat: ALEX@example.com tidak dapat diandalkan sebagai nilai berbeda dari alex@example.com menurut aturan tersebut. Tentukan kebijakan normalisasi dan perbandingan dengan sengaja, bukan menganggap ejaan tersimpan yang berbeda selalu lolos UNIQUE. Pembahasan tipe teks dan kolasi memberi konteksnya.
Pahami kunci primer dari beberapa kolom
Misalkan dua pemasok sama-sama menggunakan kode produk N001. Kunci pada kode saja akan menolak produk pemasok kedua yang sebenarnya valid. Contoh lengkap mendefinisikan pasangan (supplier_id, supplier_code) sebagai kunci primer:
| supplier_id | supplier_code | Hasil |
|---|---|---|
| 1 | N001 | Kode pemasok pertama |
| 2 | N001 | Diizinkan: gabungan berbeda |
| 1 | N001 lagi | Ditolak: gabungan sama |
INSERT INTO product_codes_key_lab VALUES (1, 'N001', 'Duplicate code');
Kunci gabungan membuat kombinasinya unik, bukan setiap komponen secara terpisah. Satu pemasok dapat memiliki beberapa kode, dan pemasok berbeda dapat berbagi kode. Kueri serta referensi kunci asing mungkin memerlukan seluruh kombinasi. Jika kunci bisnis dapat berubah, pertimbangkan pengenal stabil yang dibuat otomatis dengan kombinasi UNIQUE terpisah, alih-alih memperluas kunci primer semata-mata demi kemudahan.
Antisipasi nomor terlewat pada pengenal otomatis
Bagian auto_gap_lab menyisipkan baris dalam transaksi, membatalkannya dengan ROLLBACK, lalu menyimpan baris lain. Pada contoh InnoDB baru ini dengan pengaturan kenaikan bawaan, baris tersimpan memiliki pengenal 2 dan label Saved row. Pengenal pertama yang sudah dicadangkan oleh penyisipan yang dibatalkan tidak dikembalikan ke urutan.
Penghapusan, penyisipan gagal, dan pekerjaan bersamaan juga dapat menimbulkan nomor terlewat. AUTO_INCREMENT bukan hitungan baris atau jaminan penomoran faktur tanpa celah. Hitung baris dengan COUNT(*); MAX(id) hanya menunjukkan pengenal tertinggi yang terlihat.
Jika memerlukan pengenal yang baru dibuat, panggil LAST_INSERT_ID() pada koneksi yang sama segera setelah INSERT terkait berhasil. Pada INSERT beberapa baris, fungsi ini mengembalikan pengenal otomatis pertama yang dibuat oleh perintah tersebut, bukan daftar semua pengenal. Jangan mencampurnya dengan pekerjaan koneksi lain atau menganggap nilai setelah INSERT gagal sebagai bukti keberhasilan. Menghitung MAX(id)+1 sendiri dapat membuat klien bersamaan memilih angka berikutnya yang sama dan bukan pengganti pembuatan pengenal yang aman.
Periksa kunci dan atur ulang tabel terpisah
SHOW CREATE TABLE customers_key_lab;
SHOW INDEX FROM customers_key_lab;
SHOW CREATE TABLE product_codes_key_lab;
Pastikan PRIMARY dan uq_customers_key_email merupakan kunci terpisah serta kunci primer gabungan memuat kedua kolom yang dimaksud. Extra pada DESCRIBE dan SHOW INDEX menjawab pertanyaan berbeda: pembuatan nilai otomatis dan keunikan merupakan fitur berbeda.
Sebelum mengulang latihan, gunakan SELECT DATABASE() dan SHOW CREATE TABLE dengan setiap nama tabel sebenarnya untuk memastikan sasarannya adalah customers_key_lab, product_codes_key_lab, dan auto_gap_lab yang dibuat di sini. Cadangkan data yang perlu dipertahankan. Hanya setelah sasaran dipastikan, hapus dengan:
DROP TABLE shopcourse.customers_key_lab, shopcourse.product_codes_key_lab, shopcourse.auto_gap_lab;
Perintah ini menghapus ketiga tabel yang disebut beserta barisnya. DROP TABLE tidak dibalik dengan ROLLBACK biasa. Setelah itu, jalankan kembali contoh normal lengkap. Tabel products dari latihan produk pertama berada di luar cakupan pengaturan ulang ini.




