Panduan Relasi Database

Relasi database menghubungkan baris di satu tabel dengan baris di tabel lain melalui foreign key: kolom yang menyimpan primary key dari baris terkait. Ada tiga jenis relasi, one-to-one, one-to-many, dan many-to-many, dan ketiganya dibangun dari foreign key. Contoh-contohnya berjalan di PostgreSQL, MySQL, SQLite, dan SQL Server.

Cara membuat relasi database

Beri tabel anak sebuah kolom untuk id induknya, lalu deklarasikan sebagai foreign key agar database menolak nilai yang tidak cocok dengan baris induk yang ada:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Lakukan join pada foreign key untuk menggabungkan baris-baris yang terkait. Constraint ini juga mencegah baris yang tidak menunjuk ke mana pun:

-- Input: customers
-- id | name
-- ---+-----------
-- 1  | Ana Lima
-- 2  | Ben Okafor

-- Input: orders
-- id | customer_id | total
-- ---+-------------+------
-- 10 | 1           | 25.00
-- 11 | 2           | 15.50
-- 12 | 1           | 40.00

SELECT c.name, o.id AS order_id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

-- Output:
-- name       | order_id | total
-- -----------+----------+------
-- Ana Lima   | 10       | 25.00
-- Ben Okafor | 11       | 15.50
-- Ana Lima   | 12       | 40.00

INSERT INTO orders (id, customer_id, total) VALUES (13, 99, 10.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- "orders_customer_id_fkey"

DELETE FROM customers WHERE id = 1;
-- ERROR: update or delete on table "customers" violates foreign key constraint
-- "orders_customer_id_fkey" on table "orders"

Aturan yang perlu diketahui

• SQLite mengabaikan foreign key kecuali Anda mengaktifkannya. Jalankan PRAGMA foreign_keys = ON; di setiap koneksi, atau INSERT yang salah di atas akan tetap lolos.
• Tulis foreign key sebagai baris FOREIGN KEY (...) REFERENCES ... tersendiri, seperti di atas. MySQL 8.0 diam-diam mengabaikan REFERENCES yang ditulis langsung pada kolom (customer_id INTEGER REFERENCES customers(id)), sehingga baris yang salah langsung masuk.
• Tentukan apa yang terjadi saat induk dihapus. Secara default database akan menolak, seperti di atas. Tambahkan ON DELETE CASCADE ke foreign key untuk ikut menghapus baris anak, atau ON DELETE SET NULL untuk mempertahankannya tanpa induk.
• Buat index pada kolom foreign key. Anda akan sering melakukan join dan filter pada kolom ini, dan hanya MySQL yang otomatis membuatkan index-nya: CREATE INDEX idx_orders_customer_id ON orders (customer_id);
• Gunakan tipe yang sama dengan key yang dirujuknya, misalnya INTEGER untuk id bertipe INTEGER.
• Jangan pernah menyimpan daftar id dalam satu kolom, seperti '1,3,5'. Database tidak bisa memeriksa atau melakukan join padanya. Itu adalah relasi many-to-many, dan butuh junction table (lihat di bawah).

Sintaks alternatif

Beri nama pada constraint agar pesan error-nya lebih mudah dibaca dan lebih mudah di-drop nanti:

CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)

Untuk menambahkan foreign key ke tabel yang sudah ada, gunakan ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id);. SQLite tidak mendukung ini: buat tabel baru dengan foreign key, salin barisnya, lalu tukar tabelnya.

Untuk id yang diisi otomatis oleh database, gunakan GENERATED ALWAYS AS IDENTITY (atau SERIAL) di PostgreSQL, AUTO_INCREMENT di MySQL, IDENTITY(1,1) di SQL Server, dan INTEGER PRIMARY KEY di SQLite.

Kapan sebaiknya menggunakan relasi database?

Gunakan relasi setiap kali dua jenis data saling terhubung. Jenis relasi yang Anda butuhkan bergantung pada berapa banyak baris yang bisa ada di tiap sisi.

Pecah satu tabel menjadi dua: one-to-one

Setiap baris cocok dengan paling banyak satu baris di tabel lain. Tambahkan foreign key UNIQUE ke tabel kedua. Gunakan ini untuk memindahkan kolom opsional atau jarang dipakai dari tabel yang lebar, atau untuk menyimpan data sensitif (pengguna dan profilnya, karyawan dan catatan gajinya) dengan izin akses terpisah.

Beri satu baris banyak anak: one-to-many

Relasi yang paling umum: satu pelanggan punya banyak pesanan, satu penulis punya banyak postingan. Taruh foreign key biasa di sisi “banyak”. Induknya bahkan bisa tabel yang sama, seperti ketika employees.manager_id menunjuk ke karyawan lain.

Hubungkan banyak baris ke banyak baris lain: many-to-many

Mahasiswa mengambil banyak mata kuliah dan mata kuliah punya banyak mahasiswa, jadi tidak ada tabel yang bisa menyimpan id tabel lainnya. Tambahkan junction table dengan foreign key ke masing-masing sisi dan primary key pada pasangan tersebut, agar hubungan yang sama tidak tersimpan dua kali. Informasi tentang hubungan itu sendiri, seperti nilai, disimpan di junction table.

Temukan baris yang tidak punya pasangan di sisi lain

Relasi juga menunjukkan apa yang tidak ada. LEFT JOIN pada foreign key mempertahankan setiap baris dari tabel pertama dan mengisi NULL jika tidak ada yang cocok, sehingga filter NULL akan menemukan pelanggan tanpa pesanan atau mata kuliah yang tidak diambil siapa pun.

Contoh nyata relasi database

Pengguna dan profilnya

profiles.user_id bersifat UNIQUE, jadi seorang pengguna tidak bisa punya profil kedua. LEFT JOIN tetap menampilkan pengguna yang belum membuat profil:

-- profiles.user_id is declared as: user_id INTEGER UNIQUE NOT NULL

-- Input: users
-- id | username
-- ---+---------
-- 1  | mia
-- 2  | raj
-- 3  | zoe

-- Input: profiles
-- id | user_id | bio
-- ---+---------+---------------------
-- 1  | 1       | Software developer
-- 2  | 2       | Marketing specialist

SELECT u.username, p.bio
FROM users u
LEFT JOIN profiles p ON p.user_id = u.id
ORDER BY u.id;

-- Output:
-- username | bio
-- ---------+---------------------
-- mia      | Software developer
-- raj      | Marketing specialist
-- zoe      | NULL

INSERT INTO profiles (id, user_id, bio) VALUES (3, 1, 'Second profile');
-- ERROR: duplicate key value violates unique constraint "profiles_user_id_key"

Karyawan dan manajernya

Relasi one-to-many di dalam satu tabel: manager_id adalah foreign key ke employees.id. Join tabel dengan dirinya sendiri untuk menampilkan setiap karyawan di samping manajernya:

-- Input: employees
-- id | name      | manager_id
-- ---+-----------+-----------
-- 1  | Maya Chen | NULL
-- 2  | Sam Ortiz | 1
-- 3  | Jo Kim    | 2
-- 4  | Lee Ross  | 2

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;

-- Output:
-- employee  | manager
-- ----------+----------
-- Maya Chen | NULL
-- Sam Ortiz | Maya Chen
-- Jo Kim    | Sam Ortiz
-- Lee Ross  | Sam Ortiz

Mahasiswa dan mata kuliahnya

Junction table enrollments memiliki foreign key ke masing-masing sisi dan PRIMARY KEY (student_id, course_id). Lakukan join melaluinya untuk melihat siapa mengambil apa:

-- Input: students
-- id | name
-- ---+------
-- 1  | Priya
-- 2  | Tomas
-- 3  | Leah

-- Input: courses
-- id | title
-- ---+----------------
-- 1  | Database Design
-- 2  | Web Development
-- 3  | Statistics

-- Input: enrollments
-- student_id | course_id | grade
-- -----------+-----------+------
-- 1          | 1         | A
-- 1          | 2         | B+
-- 2          | 1         | A-
-- 3          | 2         | A

SELECT s.name AS student, c.title AS course, e.grade
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
ORDER BY s.name, c.title;

-- Output:
-- student | course          | grade
-- --------+-----------------+------
-- Leah    | Web Development | A
-- Priya   | Database Design | A
-- Priya   | Web Development | B+
-- Tomas   | Database Design | A-

INSERT INTO enrollments VALUES (1, 1, 'B');
-- ERROR: duplicate key value violates unique constraint "enrollments_pkey"

Mata kuliah yang tidak diambil siapa pun

NOT EXISTS adalah cara lain untuk menemukan baris tanpa pasangan. Query ini mempertahankan setiap mata kuliah yang tidak punya baris di junction table:

-- Input: the courses and enrollments tables above

SELECT c.title
FROM courses c
WHERE NOT EXISTS (
  SELECT 1 FROM enrollments e WHERE e.course_id = c.id
);

-- Output:
-- title
-- ----------
-- Statistics

Bacaan lanjutan tentang relasi database

Di blog ini:

• Panduan relasi one-to-one: pembahasan mendalam one-to-one
• Panduan relasi one-to-many: join, menghitung, dan mengagregasi baris anak
• Panduan relasi many-to-many: pembahasan mendalam junction table
• Panduan diagram SQL JOIN: semua jenis join yang akan Anda pakai pada relasi
• Cara menggunakan foreign key di PostgreSQL: opsi foreign key di PostgreSQL
• ON DELETE CASCADE di PostgreSQL: menghapus baris anak bersama induknya
• Composite primary key di SQL: key dua kolom yang digunakan junction table
• Panduan SQL CREATE TABLE: key dan constraint saat membuat tabel

Dokumentasi resmi:

• PostgreSQL: foreign key
• MySQL: constraint FOREIGN KEY
• SQLite: dukungan foreign key
• SQL Server: constraint primary key dan foreign key