KAL
Menu

DDL dan manajemen schema

Disiplin CREATE dan ALTER beserta constraint untuk menjaga schema yang sehat di basis data analitik.

Daftar isi

Topik ini membahas disiplin DDL (Data Definition Language), yaitu kumpulan pernyataan yang mendefinisikan struktur basis data. Pembahasan mencakup CREATE TABLE, ALTER TABLE, dan constraint yang menjaga kualitas data sejak saat penulisan. Contoh dijalankan dengan DuckDB 2.0 melalui Python, dan setiap pesan error yang ditampilkan berasal dari eksekusi nyata. Skema yang sehat bukan sekadar tabel yang berhasil dibuat, melainkan kontrak yang menolak data cacat di pintu masuk. Setelah membaca, tabel analitik dapat dibangun dengan batasan yang jelas dan perubahan skemanya tercatat.

Menyusun tabel dengan CREATE TABLE#

CREATE TABLE mendefinisikan nama tabel, kolom, tipe data, dan constraint dalam satu pernyataan, dan bentuknya paling murah diperbaiki sebelum data masuk. Skrip berikut membuat dua tabel gudang, yaitu pemasok dan barang, lengkap dengan PRIMARY KEY, NOT NULL, UNIQUE, CHECK, dan FOREIGN KEY. Setelah itu, skrip menampilkan struktur tabel serta daftar constraint yang tercatat.

python buat_tabel.py 
import os
import duckdb


def tampilkan(con, sql):
    """Cetak hasil query sebagai tabel rapi berkolom sejajar."""
    hasil = con.execute(sql)
    kolom = [d[0] for d in hasil.description]
    baris = hasil.fetchall()
    lebar = []
    for i in range(len(kolom)):
        anggota = [len(kolom[i])] + [len(str(b[i])) for b in baris]
        lebar.append(max(anggota))
    fmt = "  ".join(f"{{:<{w}}}" for w in lebar)
    print(fmt.format(*kolom).rstrip())
    for b in baris:
        print(fmt.format(*["NULL" if v is None else str(v) for v in b]).rstrip())


# basis data kecil dibangun ulang setiap kali skrip dijalankan
if os.path.exists("gudang.duckdb"):
    os.remove("gudang.duckdb")

con = duckdb.connect("gudang.duckdb")
con.execute("""
CREATE TABLE pemasok (
    id   INTEGER PRIMARY KEY,
    nama VARCHAR NOT NULL,
    kode VARCHAR UNIQUE
)
""")
con.execute("""
CREATE TABLE barang (
    id         INTEGER PRIMARY KEY,
    nama       VARCHAR NOT NULL,
    stok       INTEGER NOT NULL CHECK (stok >= 0),
    pemasok_id INTEGER REFERENCES pemasok (id)
)
""")
con.execute("""
INSERT INTO pemasok VALUES
    (1, 'pemasok pertama', 'PM01'),
    (2, 'pemasok kedua',   'PM02')
""")
con.execute("""
INSERT INTO barang VALUES
    (1, 'kopi bubuk', 120, 1),
    (2, 'gula pasir', 80,  1)
""")

print("Struktur tabel barang menurut DESCRIBE:")
tampilkan(con, "DESCRIBE barang")
print()
print("Constraint yang tercatat pada kedua tabel:")
tampilkan(con, """
SELECT table_name, constraint_type, constraint_text
FROM duckdb_constraints()
ORDER BY table_name, constraint_type, constraint_text
""")
con.close()
text Keluaran 
Struktur tabel barang menurut DESCRIBE:
column_name  column_type  null  key   default  extra
id           INTEGER      NO    PRI   NULL     NULL
nama         VARCHAR      NO    NULL  NULL     NULL
stok         INTEGER      NO    NULL  NULL     NULL
pemasok_id   INTEGER      YES   NULL  NULL     NULL

Constraint yang tercatat pada kedua tabel:
table_name  constraint_type  constraint_text
barang      CHECK            CHECK((stok >= 0))
barang      FOREIGN KEY      FOREIGN KEY (pemasok_id) REFERENCES pemasok(id)
barang      NOT NULL         NOT NULL
barang      NOT NULL         NOT NULL
barang      NOT NULL         NOT NULL
barang      PRIMARY KEY      PRIMARY KEY(id)
pemasok     NOT NULL         NOT NULL
pemasok     NOT NULL         NOT NULL
pemasok     PRIMARY KEY      PRIMARY KEY(id)
pemasok     UNIQUE           UNIQUE(kode)

Kolom key pada DESCRIBE menandai id sebagai PRI, yaitu kunci utama yang unik dan wajib terisi, sedangkan kolom null memperlihatkan kolom mana yang menerima NULL. Tampilan duckdb_constraints() mencatat setiap batasan, dan baris NOT NULL muncul satu kali per kolom karena sifatnya menempel pada kolom. CHECK membatasi rentang nilai dengan ekspresi boolean, FOREIGN KEY memastikan pemasok_id merujuk baris yang benar-benar ada di pemasok, dan UNIQUE melarang kode ganda meskipun memperbolehkan NULL. Constraint semacam ini diperiksa mesin pada setiap penulisan, sehingga aturannya tidak bergantung pada kedisiplinan penulis pipeline. Nama tabel dan kolom juga bagian dari disiplin yang sama, yaitu konsisten dalam bentuk tunggal, huruf kecil, dan pemisah garis bawah, agar query lintas tim tidak menabrak perbedaan gaya penamaan.

Constraint sebagai penjaga kualitas data#

Constraint yang terpasang menolak penulisan yang melanggar, dan pesan errornya menjelaskan batasan mana yang dilanggar. Skrip berikut mencoba lima penulisan yang cacat, menangkap errornya dengan try/except, lalu menghitung sisa baris untuk membuktikan bahwa tidak ada baris cacat yang lolos.

python uji_constraint.py 
import duckdb

con = duckdb.connect("gudang.duckdb")

percobaan = [
    ("PRIMARY KEY ganda",
     "INSERT INTO pemasok (id, nama, kode) VALUES (1, 'duplikat', 'PM99')"),
    ("UNIQUE ganda",
     "INSERT INTO pemasok (id, nama, kode) VALUES (3, 'pemasok ketiga', 'PM01')"),
    ("NOT NULL dilanggar",
     "INSERT INTO barang (id, nama, stok, pemasok_id) VALUES (11, 'susu kotak', NULL, 1)"),
    ("CHECK dilanggar",
     "INSERT INTO barang (id, nama, stok, pemasok_id) VALUES (12, 'gula cair', -5, 1)"),
    ("FOREIGN KEY menggantung",
     "INSERT INTO barang (id, nama, stok, pemasok_id) VALUES (13, 'teh celup', 40, 99)"),
]

for label, sql in percobaan:
    print(f"{label}:")
    try:
        con.execute(sql)
        print("  pernyataan dieksekusi tanpa error")
    except Exception as e:
        print(f"  {type(e).__name__}: {e}")

print()
sisa = con.execute("SELECT COUNT(*) FROM barang").fetchone()[0]
print(f"jumlah baris barang setelah semua percobaan: {sisa}")
con.close()
text Keluaran 
PRIMARY KEY ganda:
  ConstraintException: Constraint Error: Duplicate key "id: 1" violates primary key constraint.
UNIQUE ganda:
  ConstraintException: Constraint Error: Duplicate key "kode: PM01" violates unique constraint.
NOT NULL dilanggar:
  ConstraintException: Constraint Error: NOT NULL constraint failed: barang.stok
CHECK dilanggar:
  ConstraintException: Constraint Error: CHECK constraint failed on table "barang" with expression CHECK((stok >= 0))
FOREIGN KEY menggantung:
  ConstraintException: Constraint Error: Violates foreign key constraint because key "id: 99" does not exist in the referenced table

jumlah baris barang setelah semua percobaan: 2

Lima pesan error memperlihatkan lima constraint bekerja secara independen, mulai dari kunci utama ganda, kode pemasok yang sama, stok yang kosong, stok negatif, sampai pemasok yang tidak ada. Hitungan akhir tetap 2 karena pernyataan INSERT yang gagal tidak meninggalkan baris, termasuk pada INSERT multi-baris yang seluruhnya dibatalkan. Perilaku inilah yang membuat constraint sering disebut gerbang kualitas pertama, yaitu data cacat berhenti di pintu masuk dengan pesan yang jelas, bukan menyebar senyap ke laporan. Untuk pekerjaan analitik, disiplin yang sama berlaku pada tabel staging, yaitu tabel perantara sebelum data dimurnikan, karena validator di mesin basis data jauh lebih andal daripada pemeriksaan manual.

Mengubah schema dengan ALTER TABLE#

ALTER TABLE mengubah struktur tabel yang sudah berisi data, dan ADD COLUMN adalah bentuknya yang paling sering dipakai. Kolom baru pada tabel yang sudah terisi bernilai NULL untuk seluruh baris lama sampai diisi, sehingga kolom baru sebaiknya boleh NULL atau diberi nilai default. Skrip berikut menambah kolom tanggal_diperiksa pada tabel barang, lalu mengisinya.

python alter_tabel.py 
import duckdb


def tampilkan(con, sql):
    """Cetak hasil query sebagai tabel rapi berkolom sejajar."""
    hasil = con.execute(sql)
    kolom = [d[0] for d in hasil.description]
    baris = hasil.fetchall()
    lebar = []
    for i in range(len(kolom)):
        anggota = [len(kolom[i])] + [len(str(b[i])) for b in baris]
        lebar.append(max(anggota))
    fmt = "  ".join(f"{{:<{w}}}" for w in lebar)
    print(fmt.format(*kolom).rstrip())
    for b in baris:
        print(fmt.format(*["NULL" if v is None else str(v) for v in b]).rstrip())


con = duckdb.connect("gudang.duckdb")

con.execute("ALTER TABLE barang ADD COLUMN tanggal_diperiksa DATE")
print("Struktur barang sesudah kolom baru ditambahkan:")
tampilkan(con, "DESCRIBE barang")
print()
con.execute("UPDATE barang SET tanggal_diperiksa = DATE '2024-07-01'")
print("Isi kolom baru sesudah diisi:")
tampilkan(con, "SELECT id, nama, tanggal_diperiksa FROM barang ORDER BY id")
con.close()
text Keluaran 
Struktur barang sesudah kolom baru ditambahkan:
column_name        column_type  null  key   default  extra
id                 INTEGER      NO    PRI   NULL     NULL
nama               VARCHAR      NO    NULL  NULL     NULL
stok               INTEGER      NO    NULL  NULL     NULL
pemasok_id         INTEGER      YES   NULL  NULL     NULL
tanggal_diperiksa  DATE         YES   NULL  NULL     NULL

Isi kolom baru sesudah diisi:
id  nama        tanggal_diperiksa
1   kopi bubuk  2024-07-01
2   gula pasir  2024-07-01

DESCRIBE sesudah perubahan memperlihatkan tanggal_diperiksa bertipe DATE dan menerima NULL, dan pengisian massal dengan satu pernyataan UPDATE cukup untuk melengkapinya. Bentuk ALTER lain yang lazim meliputi RENAME COLUMN, DROP COLUMN, dan penambahan constraint, semuanya terdokumentasi pada referensi di bawah. Disiplin yang menjaga skema tetap sehat justru berada di sekitar pernyataannya, yaitu setiap perubahan ditulis sebagai langkah migrasi. Langkah migrasi tersimpan di repository, tempat kode proyek dikelola, lalu diuji pada salinan data sebelum dijalankan pada basis data utama. Urutan perubahan yang aman biasanya aditif terlebih dahulu, misalnya menambah kolom dan mengisinya, lalu menghapus kolom lama setelah seluruh pembaca beralih.

Kesalahan umum#

Tiga kekeliruan berikut paling sering ditemukan pada skema basis data analitik, beserta penyebab dan perbaikannya.

  • Memakai tipe VARCHAR untuk semua kolom. Tipe yang longgar menyembunyikan kesalahan data dan membuat statistik kurang akurat. Tetapkan tipe yang paling spesifik yang masih benar, misalnya INTEGER untuk stok dan DATE untuk tanggal.
  • Mengandalkan pemeriksaan manual alih-alih constraint. Pemeriksaan di aplikasi atau skrip mudah terlewat saat sumber baru muncul. Pindahkan aturan yang mendasar ke NOT NULL, UNIQUE, CHECK, atau FOREIGN KEY.
  • Mengubah skema langsung pada basis data tanpa catatan. Perubahan yang tidak tercatat sulit diulang dan penyebabnya sulit ditelusuri. Tulis setiap perubahan sebagai langkah migrasi yang tersimpan bersama kode.
  • Menambah kolom NOT NULL pada tabel yang sudah terisi tanpa nilai default. Baris lama tidak punya nilai untuk kolom baru, sehingga pernyataannya ditolak mesin. Tambahkan kolomnya sebagai kolom biasa, isi terlebih dahulu, baru terapkan NOT NULL.

Ringkasan#

  • CREATE TABLE menetapkan kontrak tabel melalui tipe data dan constraint, yaitu PRIMARY KEY, NOT NULL, UNIQUE, CHECK, dan FOREIGN KEY.
  • Constraint menolak penulisan yang melanggar dengan pesan error yang jelas, dan pernyataan yang gagal tidak meninggalkan baris.
  • ADD COLUMN menghasilkan NULL untuk baris lama, sehingga pengisiannya direncanakan sejak awal.
  • Perubahan skema dikelola sebagai migrasi yang tercatat bersama kode, diuji, lalu dijalankan secara bertahap.

Referensi#