Kursus MariaDB / Merancang Relasi Satu-ke-Banyak, Banyak-ke-Banyak, dan Kunci Asing

Rancang relasi tabel dengan menetapkan arti setiap baris, lalu hubungkan kunci primer dan kunci asing. Satu pelanggan dapat memiliki banyak pesanan; hubungan banyak-ke-banyak antara pesanan dan produk diwakili tabel rincian pesanan yang menyimpan jumlah serta harga satuan saat pembelian.

Tentukan arti baris dan arah relasi

Kunci primer mengidentifikasi satu baris, sedangkan kunci asing atau foreign key membatasi referensi ke baris pada tabel yang dirujuk. Jika nama pelanggan dan daftar produk disimpan sebagai satu teks pesanan, perhitungan jumlah memerlukan pemecahan teks dan perubahan pelanggan harus memperbarui informasi berulang. Pisahkan pelanggan, produk, pesanan, serta rinciannya berdasarkan fakta dan aturan pemeliharaan yang diperlukan.

Tabel latihan Arti satu baris Identitas atau relasi
c25_customers Satu pelanggan Kunci primer customer_id
c25_products Satu jenis produk Kunci primer product_id
c25_orders Satu pesanan customer_id merujuk pelanggan
c25_items Satu produk dalam satu pesanan Kunci primer gabungan order_id + product_id

Setiap rincian menyimpan harga satuan pembelian pada unit_price. Nilai pesanan lama tetap dapat dihitung setelah harga katalog berubah. Jika satu produk boleh muncul beberapa kali dengan pilihan berbeda dalam pesanan yang sama, pertimbangkan pengenal baris rincian atau kunci pilihan. Kunci gabungan sederhana pada latihan ini hanya mengizinkan satu baris per pasangan pesanan dan produk.

Satu pelanggan dapat mempunyai banyak pesanan; setiap rincian menghubungkan pesanan dengan produk dan menyimpan jumlah serta harga satuan pembelian

Skema mengizinkan pelanggan tanpa pesanan serta produk yang belum terjual. Setiap pesanan membutuhkan satu pelanggan; setiap rincian membutuhkan satu pesanan dan satu produk. Kunci asing sendiri tidak mewajibkan sebuah pesanan memiliki setidaknya satu rincian. Aturan minimum tersebut perlu ditangani secara terpisah. Referensi kunci asing MariaDB menjelaskan pemeriksaan integritas referensial ini.

Siapkan contoh lengkap dengan kunci asing

Hubungkan ke server belajar sebagai course_user dan pilih shopcourse. Panduan klien dan berkas SQL UTF-8 menjelaskan penyimpanan kode serta pelaksanaan dengan SOURCE C:/mariadb-lab/lesson.sql;. Ganti jalur dengan berkas yang sebenarnya dan jalankan SQL di dalam klien MariaDB.

Sebelum membuat c25_customers, c25_products, c25_orders, dan c25_items, periksa SELECT VERSION(), DATABASE(); serta SHOW TABLES;. Jika nama tersebut sudah ada, periksa definisi dan datanya dahulu. Gunakan hanya objek latihan yang boleh dibuang; pertahankan tabel aplikasi. Pastikan @@SESSION.foreign_key_checks dan @@SESSION.check_constraint_checks aktif agar pengujian benar-benar memeriksa batasan.

Buat dan isi pelanggan serta produk lebih dahulu, kemudian pesanan dan rincian yang merujuknya. Panduan kunci primer dan kunci gabungan menjelaskan pengenal baris yang digunakan di sini.

USE shopcourse;
CREATE TABLE c25_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c25_order_customer FOREIGN KEY (customer_id)
  REFERENCES c25_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c25_item_order FOREIGN KEY (order_id)
  REFERENCES c25_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c25_item_product FOREIGN KEY (product_id)
  REFERENCES c25_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c25_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c25_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c25_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c25_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

SQL mempertahankan contoh nama dan kota berbahasa Korea agar data UTF-8 dari sumber tetap konsisten. Produk 1–4 adalah buku catatan, pena, cangkir, dan lampu. Pelanggan 1 dan 3 berada di Seoul; pelanggan 2 di Busan dan pelanggan 4 di Daejeon. Pertahankan teks persis seperti contoh dan gunakan ID saat membandingkan hasil. Semua baris merupakan data latihan.

Periksa relasi satu-ke-banyak dan banyak-ke-banyak

SHOW CREATE TABLE c25_items;
SELECT c.customer_id,c.customer_name,o.order_id
FROM c25_customers AS c JOIN c25_orders AS o
 ON o.customer_id=c.customer_id ORDER BY c.customer_id,o.order_id;
SELECT COUNT(*) AS customers FROM c25_customers;
SELECT COUNT(*) AS orders_count FROM c25_orders;
SELECT COUNT(*) AS detail_rows FROM c25_items;

Hasil menunjukkan empat pelanggan, empat pesanan, dan enam rincian. Pelanggan 1 mempunyai pesanan 101 dan 102. Pesanan 101 memuat produk 1 serta 2, sedangkan produk 1 terdapat pada pesanan 101 serta 103. Setiap pasangan pesanan–produk menjadi satu baris dengan jumlah dan harga satuan historis.

Kueri JOIN mencocokkan customer_id untuk memperlihatkan relasi. Hasilnya hanya memuat pelanggan yang mempunyai pesanan cocok. Tidak munculnya pelanggan 4 dalam hasil tersebut tidak berarti baris pelanggan telah dihapus. Gunakan pengenal untuk hubungan karena nama dapat sama dan dapat berubah.

Uji referensi yang tidak ada dan penghapusan yang dibatasi

Setelah persiapan berhasil, jalankan dua percobaan kesalahan berikut secara terpisah. Perintah pertama merujuk pelanggan 999 yang tidak ada. Perintah kedua berusaha menghapus pelanggan 1 ketika pesanan yang merujuknya masih tersimpan.

INSERT INTO c25_orders(order_id,customer_id,ordered_at,status)
VALUES(999,999,'2026-03-01 10:00:00','paid');

DELETE FROM c25_customers WHERE customer_id=1;

Keduanya harus ditolak oleh pemeriksaan kunci asing. Sesudah setiap kesalahan, periksa bahwa pelanggan 1 masih ada dan jumlah pesanan tetap empat. Pisahkan percobaan kesalahan dari berkas persiapan normal karena kegagalan dapat menghentikan perintah berikutnya. Pertahankan foreign_key_checks tetap aktif ketika memeriksa masukan.

Pada desain lain, kunci asing yang mengizinkan NULL dapat menyatakan bahwa baris belum mempunyai induk. customer_id pada tabel pesanan latihan ini memakai NOT NULL sehingga keadaan itu ditolak. Panduan batasan masukan menjelaskan perbedaan antara kehadiran nilai dan persyaratan lainnya.

Pilih kebijakan penghapusan dengan sengaja

CASCADE dapat meneruskan penghapusan baris induk ke baris anak. Kemudahan saja tidak cukup untuk memilihnya: kebutuhan aplikasi mungkin mengharuskan sebagian data dipertahankan, dianonimkan, atau dihapus. Rincian pesanan juga dapat diperlukan untuk pengembalian barang dan riwayat transaksi. RESTRICT pada contoh ini mencegah pelanggan dihapus selama masih dirujuk pesanan.

Periksa kompatibilitas tipe kolom, ukuran dan tanda bilangan bulat, indeks, serta mesin penyimpanan. SHOW CREATE TABLE dan masukan yang sengaja tidak valid memberi bukti lebih kuat daripada sekadar menemukan kata REFERENCES dalam skrip. Kunci asing memeriksa referensi, tetapi tidak seluruh aturan bisnis seperti kecukupan stok.

Periksa relasi opsional dan hapus tabel anak lebih dahulu

Tentukan baris mana yang boleh ada sebelum sebuah hubungan terbentuk: pelanggan tanpa pesanan, produk yang belum terjual, atau pesanan yang harus mempunyai pelanggan. Bedakan atribut produk saat ini dari fakta pembelian. Ketika harga katalog diubah, unit_price pada rincian lama harus tetap menyimpan harga pembelian jika nilai awal pesanan perlu direproduksi.

Sebelum mengulang latihan, periksa basis data yang dipilih, SHOW CREATE TABLE untuk keempat tabel, dan baris tersimpan. Cadangkan isi yang perlu dipertahankan. Hapus hanya objek latihan artikel ini, dimulai dari tabel anak lalu tabel induk yang dirujuk. DROP TABLE menghapus definisi dan data sekaligus serta tidak dibalik dengan ROLLBACK biasa; pisahkan pembersihan dari percobaan transaksi.

DROP TABLE shopcourse.c25_items;
DROP TABLE shopcourse.c25_orders;
DROP TABLE shopcourse.c25_products;
DROP TABLE shopcourse.c25_customers;