SQLite ROWID и WITHOUT ROWID: разбираемся с ключами

У каждой таблицы есть скрытая колонка

Создаёте обычную таблицу в SQLite — и у вас уже есть колонка, которую вы не объявляли:

CREATE TABLE notes (
    title TEXT,
    body  TEXT
);

INSERT INTO notes (title, body) VALUES ('первая', 'привет');
INSERT INTO notes (title, body) VALUES ('вторая', 'мир');

SELECT rowid, title, body FROM notes;

Колонка rowid — это не выдумка. SQLite автоматически присваивает её каждой строке любой обычной таблицы, спрашивает он вас об этом или нет. Это 64-битное знаковое целое, уникальное в пределах таблицы, и именно по нему SQLite ищет строки в своём B-дереве. По сути, это позвоночник таблицы — стержень, на котором держится всё остальное.

Обычно вы её не замечаете, потому что SELECT * её не возвращает. Чтобы увидеть rowid, нужно явно указать имя колонки.

Три псевдонима ROWID

Поскольку rowid часто встречается в SQL-коде, написанном под другие СУБД, SQLite принимает сразу три имени для одной и той же колонки:

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

SELECT rowid, oid, _rowid_, title FROM notes;

rowid, oid и _rowid_ — это три имени одного и того же скрытого столбца. Если вы вдруг объявили реальный столбец с таким именем, ваш столбец имеет приоритет, а псевдоним становится недоступен — но это, пожалуй, единственный подводный камень. В повседневном коде просто пишите rowid.

INTEGER PRIMARY KEY — волшебная формула

А теперь момент, на котором спотыкаются почти все, кто пришёл из других СУБД. Если вы объявили столбец ровно как INTEGER PRIMARY KEY, он не хранится отдельно — он становится самим 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 и id — это один и тот же столбец под двумя разными именами. Если при вставке id не указан, SQLite сам подставит целое число (как правило, максимальный rowid + 1). Именно поэтому INTEGER PRIMARY KEY — самый эффективный способ сделать в таблице автоинкрементный ключ: никакого дополнительного столбца, никакого лишнего индекса, всё держится на самом rowid.

При этом написание критично. INT PRIMARY KEY — это не то же самое: INT и INTEGER ведут себя здесь по-разному:

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;

В таблице a id и rowid — это одно и то же. А в таблице b id — это обычный столбец, а rowid живёт отдельно как скрытое целое. Хуже того, b.id не заполняется автоматически при вставке — пока вы сами не присвоите значение, там будет NULL. Так что если хотите получить псевдоним rowid, пишите INTEGER PRIMARY KEY именно так, целиком.

Как узнать ROWID после вставки

После INSERT часто нужно узнать, какой rowid только что был присвоен — обычно чтобы связать с ним дочернюю запись. В SQLite для этого есть функция last_insert_rowid():

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

INSERT INTO posts (title) VALUES ('первый');
INSERT INTO posts (title) VALUES ('второй');

SELECT last_insert_rowid();

Функция возвращает rowid последней успешно вставленной строки в рамках текущего соединения. Большинство драйверов БД отдают то же значение через cursor.lastrowid или аналог. Ещё один способ получить его — конструкция RETURNING (о ней поговорим позже), которая возвращает rowid прямо из самого INSERT.

ROWID — это не навсегда

Пока строка существует, её rowid не меняется, но считать его пожизненным идентификатором нельзя. После VACUUM нумерация может смениться, а если удалить строку, её номер вполне может достаться новой записи при следующей вставке:

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;

Учтите: новая строка может получить старый rowid, а может и не получить — это зависит от версии SQLite и конкретной ситуации. Суть в том, что полагаться на вечную уникальность rowid нельзя. Если вам нужен идентификатор, который переживёт удаления, VACUUM и экспорт-импорт, объявите собственный столбец INTEGER PRIMARY KEY (он намертво закрепляет значение за строкой). А если строго требуется монотонно возрастающая последовательность без повторного использования значений — добавьте ключевое слово AUTOINCREMENT.

Таблицы WITHOUT ROWID в SQLite

Иногда rowid — это лишние накладные расходы, особенно когда ваш настоящий ключ вовсе не целое число. Возьмём, например, таблицу городов с ключом по названию: внутри неё окажется сразу две структуры — B-дерево по rowid и отдельный индекс по name, обеспечивающий первичный ключ. Конструкция WITHOUT ROWID схлопывает их в одну:

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;   -- ошибка: нет такого столбца: rowid

Теперь ключом хранения становится сам name. Поиск по name обходится без лишнего уровня косвенности, да и таблица занимает меньше места. Но за это приходится платить:

• Никаких rowid, oid или _rowid_ — этих колонок просто нет.
• last_insert_rowid() не обновляется при вставках в такую таблицу.
• Недоступны инкрементальный BLOB I/O и часть возможностей репликации.
• У таблицы обязательно должен быть объявлен PRIMARY KEY.

WITHOUT ROWID — это точечная оптимизация, а не выбор по умолчанию. Применяйте её, когда первичный ключ не целочисленный, а таблица крупная или активно пишется. Для обычных таблиц с целочисленным ключом стандартная раскладка с rowid и так оптимальна.

Как это уложить в голове

Если оставить только суть:

• У каждой обычной таблицы SQLite есть скрытый 64-битный целочисленный ключ — rowid.
• INTEGER PRIMARY KEY (именно в таком написании) делает вашу колонку его псевдонимом.
• Чтобы узнать только что присвоенное значение, используйте last_insert_rowid().
• Rowid могут переиспользоваться после удалений и перенумеровываться при VACUUM.
• Таблицы WITHOUT ROWID отказываются от скрытого ключа и работают напрямую по объявленному первичному ключу — удобно для нецелочисленных ключей, но часть возможностей теряется.

Чаще всего о rowid вообще можно не думать. Объявили id INTEGER PRIMARY KEY, отдали нумерацию SQLite — и поехали дальше. Механика начинает иметь значение, когда вы тюните хранилище, разбираете чужую схему или пытаетесь понять, почему INT PRIMARY KEY ведёт себя иначе, чем INTEGER PRIMARY KEY.

Дальше: NOT NULL и DEFAULT

С идентификацией строки разобрались — следующий слой в том, чтобы и остальные колонки содержали осмысленные значения. Эту задачу в основном решают NOT NULL и DEFAULT — о них и поговорим дальше.