ROWID w SQLite: ukryty klucz główny, aliasy i WITHOUT ROWID

Każda tabela ma tajną kolumnę

Utwórz zwykłą tabelę SQLite, a już masz jedną kolumnę, której nie deklarujesz:

CREATE TABLE notes (
    title TEXT,
    body  TEXT
);

INSERT INTO notes (title, body) VALUES ('pierwsza', 'cześć');
INSERT INTO notes (title, body) VALUES ('druga', 'świecie');

SELECT rowid, title, body FROM notes;

Ta kolumna rowid jest prawdziwa. SQLite przydziela ją każdemu wierszowi każdej zwykłej tabeli, czy o to prosisz, czy nie. To 64-bitowa liczba całkowita ze znakiem, unikalna w obrębie tabeli, i to właśnie ten klucz SQLite wykorzystuje do znajdowania wierszy w swoim B-drzewie. Traktuj ją jak kręgosłup tabeli: indeks, który utrzymuje porządek we wszystkim innym.

Zwykle jej nie widzisz, bo SELECT * jej nie obejmuje. Trzeba poprosić o nią po nazwie.

ROWID ma trzy aliasy

Ponieważ rowid tak często pojawia się w SQL pisanym dla innych baz, SQLite akceptuje trzy nazwy tej samej kolumny:

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

SELECT rowid, oid, _rowid_, title FROM notes;

rowid, oid i _rowid_ oznaczają tę samą ukrytą kolumnę. Jeśli zadeklarujesz prawdziwą kolumnę o jednej z tych nazw, twoja kolumna wygrywa, a alias staje się niedostępny, ale to jedyny haczyk. Na co dzień po prostu pisz rowid.

INTEGER PRIMARY KEY to magiczna fraza

Oto miejsce, w którym potyka się każdy, kto przychodzi z innych baz. Jeśli zadeklarujesz kolumnę dokładnie jako INTEGER PRIMARY KEY, ta kolumna nie jest przechowywana osobno: staje się 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 i id to ta sama kolumna pod dwiema nazwami. Wstawienia, które pomijają id, dostają automatycznie wybraną liczbę całkowitą (zwykle największy rowid + 1). Dlatego INTEGER PRIMARY KEY to najwydajniejszy sposób na automatycznie rosnący klucz w SQLite: bez dodatkowej kolumny i dodatkowego indeksu, jest tylko sam rowid.

Dokładna pisownia ma znaczenie. INT PRIMARY KEY to nie to samo: INT i INTEGER działają tu inaczej:

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;

W tabeli a wartości id i rowid się zgadzają. W tabeli b id to zwykła kolumna, a rowid to osobna ukryta liczba całkowita. Co gorsza, b.id nie wypełnia się automatycznie przy wstawieniu: ma wartość NULL, dopóki jej nie ustawisz. Gdy chcesz zachowania aliasu, trzymaj się INTEGER PRIMARY KEY (pełne słowo).

Pobieranie ROWID po wstawieniu

Po INSERT często chcesz poznać właśnie przydzielony rowid, zwykle po to, by powiązać z nim wiersz podrzędny. SQLite daje do tego last_insert_rowid():

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

INSERT INTO posts (title) VALUES ('pierwszy');
INSERT INTO posts (title) VALUES ('drugi');

SELECT last_insert_rowid();

Funkcja zwraca rowid ostatniego udanego wstawienia na bieżącym połączeniu. Większość sterowników baz danych udostępnia tę samą wartość jako cursor.lastrowid lub podobnie. Klauzula RETURNING (omówiona dalej) to inny sposób, by dostać tę wartość w ramach samego wstawienia.

ROWID nie są trwałe

Rowid wiersza jest stały, dopóki wiersz istnieje, ale nie jest identyfikatorem na całe życie. VACUUM może ponumerować rowid od nowa, a po usunięciu wiersza jego numer może zostać ponownie użyty przez przyszłe wstawienie:

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;

Zwróć uwagę, że nowy wiersz może, ale nie musi, użyć starego rowid, zależnie od wersji i okoliczności. Chodzi o to, że nie możesz polegać na jego wiecznej unikalności. Jeśli potrzebujesz identyfikatora, który przetrwa usunięcia, operacje VACUUM i eksporty, zadeklaruj własną kolumnę INTEGER PRIMARY KEY (która przypina wartość do wiersza) i rozważ słowo kluczowe AUTOINCREMENT, jeśli konkretnie potrzebujesz stale rosnących wartości, które nigdy nie są używane ponownie.

Tabele WITHOUT ROWID

Czasem rowid to narzut, którego nie chcesz, najczęściej wtedy, gdy twój prawdziwy klucz nie jest liczbą całkowitą. Na przykład tabela miast z kluczem po nazwie kończy z dwiema strukturami: B-drzewem rowid i osobnym indeksem na name, który wymusza klucz główny. WITHOUT ROWID łączy je w jedną:

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;   -- błąd: no such column: rowid

Teraz name jest właściwym kluczem przechowywania. Wyszukiwanie po name pomija jeden poziom pośredni, a tabela jest mniejsza. Kompromisy:

• Nie ma rowid, oid ani _rowid_: te kolumny po prostu nie istnieją.
• last_insert_rowid() nie aktualizuje się przy wstawieniach do tej tabeli.
• Przyrostowe I/O dla BLOB i kilka funkcji replikacji jest niedostępnych.
• Tabela musi mieć zadeklarowany PRIMARY KEY.

WITHOUT ROWID to przemyślana optymalizacja, a nie ustawienie domyślne. Sięgnij po nią, gdy klucz główny nie jest liczbą całkowitą, a tabela jest duża albo często zapisywana. Dla zwykłych tabel z kluczem liczbowym standardowy układ z rowid jest już optymalny.

Model myślowy

W skrócie:

• Każda zwykła tabela SQLite ma ukryty 64-bitowy klucz całkowity o nazwie rowid.
• INTEGER PRIMARY KEY (dokładnie w tej pisowni) czyni twoją kolumnę jego aliasem.
• Używaj last_insert_rowid(), by odczytać właśnie przydzieloną wartość.
• Rowid mogą być używane ponownie po usunięciach i numerowane od nowa przez VACUUM.
• Tabele WITHOUT ROWID rezygnują z ukrytego klucza i bezpośrednio używają zadeklarowanego klucza głównego: przydatne dla kluczy innych niż liczbowe, ale tracą niektóre funkcje.

Przez większość czasu w ogóle nie myślisz o rowid. Deklarujesz id INTEGER PRIMARY KEY, pozwalasz SQLite zająć się numeracją i idziesz dalej. Mechanika ma znaczenie, gdy stroisz przechowywanie, czytasz istniejące schematy albo zastanawiasz się, czemu INT PRIMARY KEY zachowuje się inaczej niż INTEGER PRIMARY KEY.

Dalej: NOT NULL i DEFAULT

Skoro tożsamość wiersza jest już ustalona, kolejna warstwa to pilnowanie, by pozostałe kolumny miały sensowne wartości. NOT NULL i DEFAULT to dwie klauzule, które wykonują większość tej pracy. O nich za chwilę.