ROWID SQLite: Key Tersembunyi dan WITHOUT ROWID

Setiap Tabel Punya Kolom Rahasia

Buat tabel SQLite biasa, dan kamu sudah punya satu kolom yang tidak kamu deklarasikan:

CREATE TABLE notes (
    title TEXT,
    body  TEXT
);

INSERT INTO notes (title, body) VALUES ('first', 'hello');
INSERT INTO notes (title, body) VALUES ('second', 'world');

SELECT rowid, title, body FROM notes;

Kolom rowid itu nyata. SQLite memberikannya ke setiap baris di setiap tabel biasa, entah kamu memintanya atau tidak. Kolom ini berupa integer 64-bit bertanda, unik di dalam tabel, dan merupakan key sebenarnya yang dipakai SQLite untuk menemukan baris di penyimpanan B-tree-nya. Anggap saja sebagai tulang punggung tabel: index yang menjaga semua hal lain tetap teratur.

Biasanya kamu tidak melihatnya karena SELECT * tidak menyertakannya. Kamu harus memintanya dengan menyebut namanya.

ROWID Punya Tiga Alias

Karena rowid sering muncul di SQL yang ditulis untuk database lain, SQLite menerima tiga nama untuk kolom yang sama:

CREATE TABLE notes (title TEXT);
INSERT INTO notes VALUES ('a'), ('b'), ('c');

SELECT rowid, oid, _rowid_, title FROM notes;

rowid, oid, dan _rowid_ semuanya merujuk ke kolom tersembunyi yang sama. Jika kamu sudah mendeklarasikan kolom sungguhan dengan salah satu nama itu, kolommu yang menang dan aliasnya tidak bisa dipakai, tapi hanya itu jebakannya. Di kode sehari-hari, cukup tulis rowid.

INTEGER PRIMARY KEY Adalah Frasa Ajaibnya

Inilah bagian yang menjebak semua orang yang datang dari database lain. Jika kamu mendeklarasikan sebuah kolom persis sebagai INTEGER PRIMARY KEY, kolom itu tidak disimpan secara terpisah, melainkan menjadi rowid:

CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT
);

INSERT INTO users (name) VALUES ('Ada'), ('Boris'), ('Rosa');

SELECT rowid, id, name FROM users;

rowid dan id adalah kolom yang sama dengan dua nama. Insert yang tidak menyertakan id mendapat integer yang dipilih otomatis (biasanya rowid terbesar + 1). Itulah sebabnya INTEGER PRIMARY KEY adalah cara paling efisien untuk memberi tabel key yang bertambah otomatis di SQLite: tidak ada kolom tambahan, tidak ada index tambahan, hanya rowid itu sendiri.

Ejaannya harus persis. INT PRIMARY KEY tidak sama, karena INT dan INTEGER punya efek yang berbeda di sini:

CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE b (id INT PRIMARY KEY, name TEXT);

INSERT INTO a (name) VALUES ('x');
INSERT INTO b (name) VALUES ('y');

SELECT rowid, id, name FROM a;
SELECT rowid, id, name FROM b;

Di tabel a, id dan rowid cocok. Di tabel b, id adalah kolom biasa dan rowid adalah integer tersembunyi yang terpisah. Lebih parah lagi, b.id tidak terisi otomatis saat insert: nilainya NULL sampai kamu mengaturnya. Tetap pakai INTEGER PRIMARY KEY (kata lengkapnya) jika kamu ingin perilaku alias itu.

Mendapatkan ROWID dari Sebuah Insert

Setelah INSERT, kamu sering ingin tahu rowid yang baru saja diberikan, biasanya untuk menghubungkan baris anak ke baris itu. SQLite memberimu last_insert_rowid():

CREATE TABLE posts (
    id    INTEGER PRIMARY KEY,
    title TEXT
);

INSERT INTO posts (title) VALUES ('first');
INSERT INTO posts (title) VALUES ('second');

SELECT last_insert_rowid();

Fungsi ini mengembalikan rowid dari insert terakhir yang berhasil pada koneksi saat ini. Kebanyakan driver database menyediakan nilai yang sama sebagai cursor.lastrowid atau yang serupa. Klausa RETURNING (dibahas nanti) adalah cara lain untuk mendapatkannya kembali sebagai bagian dari insert itu sendiri.

ROWID Tidak Permanen

Rowid sebuah baris stabil selama baris itu ada, tapi bukan pengenal seumur hidup. VACUUM bisa menomori ulang rowid, dan jika kamu menghapus sebuah baris, nomornya bisa dipakai ulang oleh insert berikutnya:

CREATE TABLE items (name TEXT);
INSERT INTO items VALUES ('a'), ('b'), ('c');

DELETE FROM items WHERE name = 'c';
INSERT INTO items VALUES ('d');

SELECT rowid, name FROM items;

Perhatikan bahwa baris baru itu mungkin memakai ulang rowid lama atau tidak, tergantung versi dan keadaannya. Intinya, kamu tidak bisa mengandalkannya tetap unik selamanya. Jika kamu butuh pengenal yang bertahan melewati penghapusan, vacuum, dan ekspor, deklarasikan kolom INTEGER PRIMARY KEY milikmu sendiri (yang mengunci nilainya ke baris itu), dan pertimbangkan kata kunci AUTOINCREMENT jika kamu memang butuh nilai yang selalu naik dan tidak pernah dipakai ulang.

Tabel WITHOUT ROWID

Terkadang rowid adalah beban tambahan yang tidak kamu inginkan, paling sering saat key sebenarnya bukan integer. Tabel kota dengan nama sebagai key, misalnya, akhirnya punya dua struktur: B-tree rowid, dan index terpisah pada name untuk menegakkan primary key. WITHOUT ROWID menggabungkan keduanya menjadi satu:

CREATE TABLE cities (
    name       TEXT PRIMARY KEY,
    population INTEGER
) WITHOUT ROWID;

INSERT INTO cities VALUES ('Lisbon', 545000), ('Paris', 2102000);

SELECT * FROM cities WHERE name = 'Lisbon';
-- SELECT rowid FROM cities;   -- error: no such column: rowid

Sekarang name adalah key penyimpanan yang sebenarnya. Pencarian berdasarkan name melewati satu lapisan perantara, dan tabelnya lebih kecil. Konsekuensinya:

• Tidak ada rowid, oid, atau _rowid_; kolom-kolom itu memang tidak ada.
• last_insert_rowid() tidak diperbarui untuk insert pada tabel ini.
• Incremental BLOB I/O dan beberapa fitur replikasi tidak tersedia.
• Tabel itu wajib punya PRIMARY KEY yang dideklarasikan.

WITHOUT ROWID adalah optimasi yang terukur, bukan default. Pakai saat primary key-nya bukan integer dan tabelnya besar atau sering ditulis. Untuk tabel biasa dengan key integer, tata letak rowid yang biasa sudah optimal.

Model Berpikirnya

Jika diringkas ke intinya:

• Setiap tabel SQLite biasa punya key integer 64-bit tersembunyi bernama rowid.
• INTEGER PRIMARY KEY (dengan ejaan persis) menjadikan kolommu alias untuknya.
• Pakai last_insert_rowid() untuk membaca nilai yang baru saja diberikan.
• Rowid bisa dipakai ulang setelah penghapusan dan dinomori ulang oleh VACUUM.
• Tabel WITHOUT ROWID membuang key tersembunyi itu dan langsung memakai primary key yang kamu deklarasikan. Berguna untuk key non-integer, tapi beberapa fitur jadi hilang.

Sering kali kamu sama sekali tidak memikirkan rowid. Kamu mendeklarasikan id INTEGER PRIMARY KEY, membiarkan SQLite mengurus penomorannya, lalu lanjut. Mekanismenya baru penting saat kamu menyetel penyimpanan, membaca skema yang sudah ada, atau bertanya-tanya kenapa INT PRIMARY KEY berperilaku berbeda dari INTEGER PRIMARY KEY.

Selanjutnya: NOT NULL dan DEFAULT

Sekarang identitas baris sudah beres, lapisan berikutnya adalah memastikan kolom-kolom lainnya berisi nilai yang masuk akal. NOT NULL dan DEFAULT adalah dua klausa yang mengerjakan sebagian besar tugas itu, dan keduanya dibahas berikutnya.