Constraint pada Tabel

Struktur kolom yang dibahas Chapter Membuat Tabel dengan CREATE TABLE hanya mendeskripsikan bentuk data — belum ada satu pun aturan yang menjaganya. Tanpa aturan tersebut, tabel dengan senang hati menerima produk tanpa nama, harga negatif, atau dua pelanggan yang sama persis. Constraint adalah mekanisme yang memindahkan aturan bisnis ke dalam engine SQLite: pelanggaran ditolak saat itu juga dengan pesan error yang jelas, bukan dibiarkan mengendap sampai merusak laporan berbulan-bulan kemudian.

PRIMARY KEY #

PRIMARY KEY menandai kolom sebagai identitas unik sebuah baris. Pada SQLite, kolom INTEGER PRIMARY KEY punya kedudukan istimewa: kolom tersebut menjadi alias untuk rowid — nomor baris internal yang selalu ada pada setiap tabel. Konsekuensinya, dua kemudahan otomatis muncul:

sqlite> CREATE TABLE pelanggan (
   ...>   id INTEGER PRIMARY KEY,
   ...>   nama TEXT NOT NULL,
   ...>   kota TEXT
   ...> );
sqlite> INSERT INTO pelanggan (id, nama, kota) VALUES (NULL, 'Budi', 'Jakarta');
sqlite> SELECT id, rowid, nama FROM pelanggan;
1|1|Budi
sqlite>

Pertama, memasukkan NULL ke kolom id membuat engine mengisi nomor unik secara otomatis. Kedua, nilai id dan rowid selalu identik — kolom id hanya nama lain untuk nomor baris itu. Nomor berikutnya dihitung dari rowid terbesar yang ada ditambah satu, jadi setelah kita memasukkan id eksplisit 99, insert berikutnya mendapat 100:

sqlite> INSERT INTO pelanggan (id, nama, kota) VALUES (99, 'Joko', 'Medan');
sqlite> INSERT INTO pelanggan (nama, kota) VALUES ('Rina', 'Solo');
sqlite> SELECT id, nama FROM pelanggan;
1|Budi
99|Joko
100|Rina
sqlite>

Kolom id 100 tidak pernah kita ketik — engine mengambil rowid terbesar (99) lalu menambah satu. Perilaku ini berlaku untuk semua tabel dengan INTEGER PRIMARY KEY, termasuk tabel produk yang akan kita bangun berikutnya.

NOT NULL dan UNIQUE #

Kedua constraint ini paling sering dipakai sehari-hari. Sebagai contoh lengkap, berikut tabel produk dengan paket constraint penuh yang akan menemani kita sampai akhir Section ini:

CREATE TABLE produk (
    id INTEGER PRIMARY KEY,
    nama TEXT NOT NULL UNIQUE,
    harga INTEGER NOT NULL CHECK (harga >= 0),
    stok INTEGER DEFAULT 0
);

NOT NULL menolak baris tanpa nilai, UNIQUE menolak nilai yang sudah dipakai baris lain, dan keduanya melapor dengan pesan yang menyebut tabel serta kolom penyebab:

sqlite> INSERT INTO produk (harga) VALUES (25000);
Runtime error: NOT NULL constraint failed: produk.nama (19)
sqlite> INSERT INTO produk (nama, harga) VALUES ('Pensil', 3000);
sqlite> INSERT INTO produk (nama, harga) VALUES ('Pensil', 3500);
Runtime error: UNIQUE constraint failed: produk.nama (19)
sqlite>

Insert pertama gagal karena kolom nama tidak disebut dan tidak punya default, sehingga bernilai NULL. Insert ketiga gagal karena nama 'Pensil' sudah dipakai baris yang berhasil masuk sebelumnya.

DEFAULT dan CHECK #

DEFAULT mengisi nilai ketika kolom tidak disebut dalam INSERT, sedangkan CHECK mengevaluasi ekspresi boolean untuk setiap baris baru — nilai false ditolak:

sqlite> INSERT INTO produk (nama, harga) VALUES ('Penghapus', 2500);
sqlite> SELECT nama, harga, stok FROM produk WHERE nama = 'Penghapus';
Penghapus|2500|0
sqlite> INSERT INTO produk (nama, harga) VALUES ('Bunga Rusak', -500);
Runtime error: CHECK constraint failed: harga >= 0 (19)
sqlite>

Insert pertama tidak menyebut stok, jadi engine mengisi 0 dari DEFAULT. Insert kedua menabrak CHECK (harga >= 0) — engine menolak harga negatif tepat pada saat insert, bukan saat laporan keuangan mulai aneh.

Constraint Kolom vs Constraint Tabel #

Semua constraint di atas ditulis menempel pada satu kolom. Constraint juga bisa ditulis di akhir daftar kolom — berguna untuk aturan yang melibatkan lebih dari satu kolom:

sqlite> CREATE TABLE pelanggan_baru (
   ...>   id INTEGER PRIMARY KEY,
   ...>   nama TEXT NOT NULL,
   ...>   kota TEXT,
   ...>   UNIQUE (nama, kota)
   ...> );
sqlite> INSERT INTO pelanggan_baru VALUES (NULL, 'Dewi', 'Solo');
sqlite> INSERT INTO pelanggan_baru VALUES (NULL, 'Dewi', 'Solo');
Runtime error: UNIQUE constraint failed: pelanggan_baru.nama, pelanggan_baru.kota (19)
sqlite>

UNIQUE (nama, kota) berarti kombinasi keduanya yang harus unik: dua orang bernama Dewi boleh hidup berdampingan asal beda kota. Pesan error-nya pun menyebut kedua kolom yang bertabrakan. Bentuk tabel seperti ini juga bisa dipakai untuk PRIMARY KEY (a, b) dan CHECK yang menghubungkan beberapa kolom.

AUTOINCREMENT dan Alasan Menghindarinya #

Kata kunci AUTOINCREMENT terlihat seperti syarat wajib untuk id otomatis — padahal tidak. Perilaku keduanya berbeda hanya pada satu skenario: setelah baris dengan rowid terbesar dihapus, tabel biasa memakai ulang nomor tersebut, sedangkan AUTOINCREMENT melarangnya:

sqlite> CREATE TABLE catatan (id INTEGER PRIMARY KEY, isi TEXT);
sqlite> INSERT INTO catatan (isi) VALUES ('a'), ('b'), ('c');
sqlite> DELETE FROM catatan WHERE id = 3;
sqlite> INSERT INTO catatan (isi) VALUES ('d');
sqlite> SELECT id, isi FROM catatan;
1|a
2|b
3|d
sqlite> CREATE TABLE catatan_ai (id INTEGER PRIMARY KEY AUTOINCREMENT, isi TEXT);
sqlite> INSERT INTO catatan_ai (isi) VALUES ('a'), ('b'), ('c');
sqlite> DELETE FROM catatan_ai WHERE id = 3;
sqlite> INSERT INTO catatan_ai (isi) VALUES ('d');
sqlite> SELECT id, isi FROM catatan_ai;
1|a
2|b
4|d
sqlite>

Pada tabel biasa, angka 3 yang baru saja dihapus langsung dipakai ulang. Pada tabel AUTOINCREMENT, engine mencatat nomor tertinggi yang pernah terpakai di tabel khusus sqlite_sequence, sehingga insert berikutnya melompat ke 4 dan nomor yang pernah terpakai tidak akan pernah muncul lagi.

Tabel dengan pertahanan lengkap kini siap diisi. Chapter Memasukkan Data dengan INSERT mulai mengisi pelanggan dan produk dengan data toko kita.