Primary Key (PK)

Primary key adalah kolom (atau kombinasi kolom) yang secara unik mengidentifikasi setiap baris dalam tabel.

Jenis Primary Key

-- 1. Auto-increment (paling umum)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- 2. UUID (untuk distributed systems)
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    total DECIMAL(10,2)
);

-- 3. Natural key (dari data bisnis)
CREATE TABLE countries (
    code CHAR(2) PRIMARY KEY, -- "ID", "US", "JP"
    name VARCHAR(100)
);

-- 4. Composite key (gabungan kolom)
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

Foreign Key (FK)

Foreign key adalah kolom yang mereferensikan primary key di tabel lain, membentuk relasi antar tabel.

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE    -- hapus posts jika user dihapus
        ON UPDATE CASCADE    -- update FK jika PK berubah
);

ON DELETE Options

• CASCADE — hapus child records saat parent dihapus
• SET NULL — set FK jadi NULL saat parent dihapus
• RESTRICT — tolak penghapusan jika masih ada child records
• SET DEFAULT — set FK ke nilai default

Laravel Migration: constrained()

Di Laravel, gunakan method ->constrained() untuk membuat foreign key secara ringkas:

// Laravel migration
$table->foreignId("user_id")->constrained()->onDelete("cascade");
// Otomatis mereferensikan tabel "users" kolom "id"