Primary dan Foreign Keys

Pada chapter sebelumnya kita membuat tiga tabel blog beserta relasinya. Namun mendeklarasikan foreign key saja belum tentu cukup: ada kondisi ketika database diam saja menerima data yang seharusnya mustahil — duplikat id, artikel tanpa penulis, atau komentar yang menunjuk ke artikel yang tidak pernah ada. Primary key dan foreign key adalah dua mekanisme utama yang membuat database ikut menjaga kebenaran data, bukan sekadar menyimpannya. Pada chapter ini kita pelajari aturan kedua jenis key tersebut, lalu membuktikan setiap klaimnya dengan percobaan langsung pada SQLite. Hasilnya, Anda memahami bukan hanya cara mendeklarasikan key, tetapi juga apa yang benar-benar terjadi ketika aturannya dilanggar.

Aturan Dasar Primary Key #

Primary key adalah kolom — atau gabungan kolom — yang menjadi identitas unik setiap baris. Database menuntut dua hal dari primary key: nilainya tidak boleh duplikat, dan nilainya tidak boleh NULL. Sebagai imbalannya, database otomatis membuat index pada kolom primary key sehingga pencarian berdasarkan id berjalan sangat cepat. Satu tabel hanya boleh memiliki satu primary key, tetapi satu primary key boleh terdiri dari beberapa kolom — bentuk yang disebut composite key dan akan kita coba di bagian berikutnya.

INTEGER PRIMARY KEY sebagai Alias rowid #

SQLite menyimpan setiap baris tabel dengan nomor internal bernama rowid. Ketika sebuah kolom dideklarasikan sebagai INTEGER PRIMARY KEY, kolom tersebut menjadi alias rowid — dan konsekuensinya sangat praktis: jika kita tidak mengisi kolom id (atau secara eksplisit memasukkan NULL), SQLite mengisinya sendiri dengan nomor baris berikutnya. Perilaku ini membuat id integer “menumbuh” sendiri tanpa perlu penghitung di sisi aplikasi. Mari kita buktikan:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec("CREATE TABLE categories (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
)");

$stmt = $pdo->prepare("INSERT INTO categories (name) VALUES (?)");
foreach (["Teknologi", "Olahraga", "Kuliner"] as $name) {
    $stmt->execute([$name]);
}

// NULL pada INTEGER PRIMARY KEY diganti rowid baru oleh SQLite
$pdo->exec("INSERT INTO categories (id, name) VALUES (NULL, 'Seni')");

foreach ($pdo->query("SELECT id, name FROM categories ORDER BY id") as $row) {
    echo "id={$row['id']} name={$row['name']}\n";
}

Output yang Dihasilkan:

id=1 name=Teknologi
id=2 name=Olahraga
id=3 name=Kuliner
id=4 name=Seni

Tiga penyisipan pertama sama sekali tidak menyentuh kolom id, dan empat baris hasil query menunjukkan id 1 sampai 4 terisi rapi. Penyisipan keempat sengaja memasukkan NULL pada kolom id — alih-alih tersimpan NULL, SQLite menggantinya dengan rowid baru. Dengan begitu, id tetap terjamin unik dan tidak pernah NULL, sesuai tuntutan primary key.

Primary Key Komposit #

Kadang satu kolom tidak cukup menjadi identitas baris. Contoh klasiknya adalah tabel penanda relasi many-to-many antara artikel dan tag, misalnya post_tags: satu artikel boleh memiliki banyak tag, dan satu tag boleh dipakai banyak artikel. Yang harus unik adalah pasangan (post_id, tag_id) — bukan masing-masing kolomnya. Deklarasi PRIMARY KEY (post_id, tag_id) menyampaikan aturan tersebut:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec("CREATE TABLE post_tags (
    post_id INTEGER NOT NULL,
    tag_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id)
)");

$stmt = $pdo->prepare("INSERT INTO post_tags (post_id, tag_id) VALUES (?, ?)");
$stmt->execute([1, 10]);
$stmt->execute([1, 20]);
$stmt->execute([2, 10]);

try {
    $stmt->execute([1, 10]); // pasangan yang sama, harus ditolak
} catch (PDOException $e) {
    echo "Ditolak: " . $e->getMessage() . "\n";
}

echo "Jumlah baris: " . $pdo->query("SELECT COUNT(*) FROM post_tags")->fetchColumn() . "\n";

Output yang Dihasilkan:

Ditolak: SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: post_tags.post_id, post_tags.tag_id
Jumlah baris: 3

Penyisipan keempat mengulang pasangan post_id=1, tag_id=10 yang sudah ada, sehingga database menolaknya dengan pesan UNIQUE constraint failed yang menyebut kedua kolom key. Perhatikan bahwa pasangan (2, 10) sebelumnya justru diterima — tag_id 10 boleh dipakai lagi oleh artikel lain, karena pasangannya berbeda. Jumlah baris tetap tiga karena transaksi yang ditolak tidak pernah tersimpan.

Foreign Key dan Referential Integrity #

Primary key menjaga identitas di dalam satu tabel; foreign key menjaga kesahihan tautan antar tabel. Foreign key adalah kolom yang nilainya harus cocok dengan primary key di tabel lain — posts.user_id harus merujuk ke users.id yang benar-benar ada. Tujuan akhirnya disebut referential integrity: tidak boleh ada baris anak yang menunjuk ke baris induk yang tidak ada, karena baris seperti itu disebut orphan dan membuat query JOIN kehilangan data.

Mari kita amati perbedaan perilaku sebelum dan sesudah pragma tersebut diaktifkan:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec("CREATE TABLE users (
    id       INTEGER PRIMARY KEY,
    username TEXT NOT NULL
)");

$pdo->exec("CREATE TABLE posts (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    title   TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
)");

echo "Penegakan FK bawaan: " . $pdo->query("PRAGMA foreign_keys")->fetchColumn() . "\n";

// Tanpa penegakan FK, user_id yang tidak ada tetap diterima
$pdo->exec("INSERT INTO posts (user_id, title) VALUES (99, 'Posting Hantu')");
echo "Insert user_id=99 diterima (FK belum aktif)\n";

$pdo->exec("PRAGMA foreign_keys = ON");
echo "Penegakan FK setelah ON: " . $pdo->query("PRAGMA foreign_keys")->fetchColumn() . "\n";

$pdo->exec("INSERT INTO users (username) VALUES ('budi')");

try {
    $pdo->exec("INSERT INTO posts (user_id, title) VALUES (77, 'Posting Hantu Lain')");
} catch (PDOException $e) {
    echo "Ditolak database: " . $e->getMessage() . "\n";
}

$pdo->exec("INSERT INTO posts (user_id, title) VALUES (1, 'Posting Sah')");
echo "Posting milik user_id=1 berhasil disimpan\n";

$orphans = $pdo->query(
    "SELECT COUNT(*) FROM posts p
     LEFT JOIN users u ON u.id = p.user_id
     WHERE u.id IS NULL"
)->fetchColumn();
echo "Posting tanpa penulis (orphan): $orphans\n";

Output yang Dihasilkan:

Penegakan FK bawaan: 0
Insert user_id=99 diterima (FK belum aktif)
Penegakan FK setelah ON: 1
Ditolak database: SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed
Posting milik user_id=1 berhasil disimpan
Posting tanpa penulis (orphan): 1

Urutan kejadian pada output tersebut layak dibaca ulang:

1. Nilai 0 pada baris pertama menegaskan penegakan FK memang mati secara bawaan; karena itu insert user_id=99 diterima tanpa suara — inilah bahaya senyap yang menghasilkan orphan.
2. Setelah PRAGMA foreign_keys = ON, nilai berubah menjadi 1, dan insert user_id=77 ditolak database dengan PDOException bertanda SQLSTATE[23000] (kelas integrity constraint violation).
3. Insert yang sah (user_id=1, milik user Budi yang sudah ada) tetap diterima, jadi penegakan FK hanya menolak data mustahil.
4. Query terakhir menghitung orphan dengan pola LEFT JOIN ... WHERE u.id IS NULL dan menemukan satu baris: posting hantu yang lolos sebelum pragma diaktifkan.

Perilaku ON DELETE #

Saat merancang relasi, ada satu pertanyaan penting: apa yang harus terjadi pada baris anak ketika baris induknya dihapus? Jawabannya ditentukan klausul ON DELETE pada deklarasi foreign key. Pilihan perilaku yang paling sering dipakai:

Pilih CASCADE ketika hidup baris anak tidak bermakna tanpa induknya — komentar dan posting ikut terhapus bersama akunnya adalah contoh klasik. Berikut buktinya:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("PRAGMA foreign_keys = ON");

$pdo->exec("CREATE TABLE users (
    id       INTEGER PRIMARY KEY,
    username TEXT NOT NULL
)");

$pdo->exec("CREATE TABLE posts (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    title   TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)");

$pdo->exec("INSERT INTO users (username) VALUES ('budi')");
$pdo->exec("INSERT INTO users (username) VALUES ('siti')");
$pdo->exec("INSERT INTO posts (user_id, title) VALUES (1, 'Artikel Pertama')");
$pdo->exec("INSERT INTO posts (user_id, title) VALUES (1, 'Artikel Kedua')");

echo "Sebelum hapus: " . $pdo->query("SELECT COUNT(*) FROM posts")->fetchColumn() . " posting\n";

$pdo->exec("DELETE FROM users WHERE username = 'budi'");

echo "Setelah hapus user budi: "
    . $pdo->query("SELECT COUNT(*) FROM posts")->fetchColumn() . " posting\n";
echo "User tersisa: " . $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn() . "\n";

Output yang Dihasilkan:

Sebelum hapus: 2 posting
Setelah hapus user budi: 0 posting
User tersisa: 1

Penghapusan user Budi otomatis membawa serta dua artikel miliknya, sementara user Siti tetap utuh. Tanpa CASCADE, kita harus menghapus baris anak secara manual terlebih dahulu — langkah yang mudah terlupakan dan meninggalkan orphan.

SET NULL dipakai ketika relasi bersifat opsional: baris anak tetap berarti meski induknya hilang. Kolom foreign key-nya harus bisa bernilai NULL (tanpa NOT NULL), karena itulah nilai yang diisikan. Contoh berikut menghapus satu departemen dan membiarkan karyawannya tetap ada:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("PRAGMA foreign_keys = ON");

$pdo->exec("CREATE TABLE departments (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
)");

$pdo->exec("CREATE TABLE employees (
    id            INTEGER PRIMARY KEY,
    name          TEXT NOT NULL,
    department_id INTEGER REFERENCES departments(id) ON DELETE SET NULL
)");

$pdo->exec("INSERT INTO departments (name) VALUES ('Teknologi')");
$pdo->exec("INSERT INTO departments (name) VALUES ('Pemasaran')");
$pdo->exec("INSERT INTO employees (name, department_id) VALUES ('Budi', 1)");
$pdo->exec("INSERT INTO employees (name, department_id) VALUES ('Siti', 2)");

$pdo->exec("DELETE FROM departments WHERE id = 1");

foreach ($pdo->query("SELECT name, department_id FROM employees ORDER BY id") as $row) {
    $departemen = $row["department_id"] === null
        ? "tanpa departemen"
        : "departemen #{$row['department_id']}";
    echo $row["name"] . " - " . $departemen . "\n";
}

Output yang Dihasilkan:

Budi - tanpa departemen
Siti - departemen #2

Departemen Teknologi hilang, dan kolom department_id Budi berubah menjadi NULL — bukan 0, bukan string kosong, melainkan NULL yang benar-benar berarti “tidak ada nilai”. Siti yang berada di departemen Pemasaran tidak tersentuh. Query IS NULL menjadi cara resmi menemukan baris-baris semacam ini.

RESTRICT adalah kebalikan CASCADE: database menolak menghapus induk yang masih punya anak, sehingga keputusan “hapus atau tidak” diambil secara sadar oleh aplikasi. Contoh berikut menunjukkan penolakan tersebut lalu urutan penghapusan yang benar:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("PRAGMA foreign_keys = ON");

$pdo->exec("CREATE TABLE categories (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
)");

$pdo->exec("CREATE TABLE products (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    category_id INTEGER NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
)");

$pdo->exec("INSERT INTO categories (name) VALUES ('Elektronik')");
$pdo->exec("INSERT INTO products (name, category_id) VALUES ('Laptop', 1)");

try {
    $pdo->exec("DELETE FROM categories WHERE id = 1");
} catch (PDOException $e) {
    echo "Ditolak database: " . $e->getMessage() . "\n";
}

// Hapus produknya dulu, baru kategori bisa dihapus
$pdo->exec("DELETE FROM products WHERE category_id = 1");
$pdo->exec("DELETE FROM categories WHERE id = 1");
echo "Kategori berhasil dihapus setelah produknya tidak ada\n";

Output yang Dihasilkan:

Ditolak database: SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed
Kategori berhasil dihapus setelah produknya tidak ada

Penghapusan kategori Elektronik ditolak karena masih ada produk Laptop yang menunjuk ke kategori tersebut. Setelah produknya dihapus lebih dulu, penghapusan kategori berhasil. Perilaku NO ACTION (bawaan bila klausul tidak ditulis) pada praktiknya juga menolak; bedanya hanya pada waktu pemeriksaan yang sedikit berbeda di tingkat mesin SQL. Terakhir, perlu diketahui bahwa pasangan ON UPDATE juga tersedia untuk menangani perubahan nilai primary key induk — meski jarang dipakai karena id yang sudah terlanjur menjadi identitas sebaiknya tidak diubah.

Ringkasan #

Primary key menjamin identitas baris: nilainya unik dan tidak pernah NULL, dengan INTEGER PRIMARY KEY di SQLite yang tumbuh otomatis sebagai alias rowid. Foreign key menjamin kesahihan relasi antar tabel — dengan catatan penting bahwa SQLite menegakkannya hanya setelah PRAGMA foreign_keys = ON. Klausul ON DELETE kemudian menentukan nasib data anak ketika induk dihapus: CASCADE mengikuti, SET NULL melepaskan, dan RESTRICT menjaga. Dengan key yang tepat, banyak kelas bug lenyap sebelum sempat terjadi. Pertanyaan besar berikutnya adalah bagaimana memecah data ke tabel-tabel yang benar agar tidak penuh duplikasi — pekerjaan normalization pada chapter selanjutnya.

Referensi #

• PDO — php.net
• Prepared Statements PDO — php.net
• Dukungan Foreign Key — SQLite
• CREATE TABLE — SQLite