1. Обзор

1. Ключевое слово AUTOINCREMENT налагает дополнительные издержки CPU, памяти, дискового пространства и дискового I/O и не должно использоваться, если не строго необходимо.

2. В SQLite столбец с типом INTEGER PRIMARY KEY это псевдоним для ROWID (кроме таблиц WITHOUT ROWID), который всегда является 64-битным целым числом со знаком.

3. При INSERT, если столбцу ROWID или INTEGER PRIMARY KEY не будет явно задано значение, то это будет заполнено автоматически неиспользованным целым числом, обычно на единицу больше, чем самое большое использующееся ROWID. Это верно независимо от того, используется ли ключевое слово AUTOINCREMENT.

4. Если ключевое слово AUTOINCREMENT появляется после INTEGER PRIMARY KEY, это изменяет автоматический алгоритм назначения ROWID, чтобы предотвратить повторное использование ROWID в БД. Другими словами, цель AUTOINCREMENT состоит в том, чтобы предотвратить повторное использование ROWID от ранее удаленных строк.

2. Фон

В SQLite у строк таблицы обычно есть 64-битное целое число со знаком ROWID, который уникален среди всех строк в той же самой таблице (исключение: таблицы WITHOUT ROWID).

Можно получить доступ к ROWID таблицы SQLite, используя одно из специальных имен столбцов ROWID, _ROWID_ или OID. Кроме того, если вы объявите, что обычный столбец таблицы использует одно из тех специальных имен, тогда использование того имени обратится к заявленной колонке, а не к внутреннему ROWID.

Если таблица содержит колонку типа INTEGER PRIMARY KEY, то та колонка становится псевдонимом для ROWID. Можно тогда получить доступ к ROWID, используя любое из четырех различных имен, оригинальные три имени, описанные выше или имя, данное столбцу INTEGER PRIMARY KEY. Все эти имена псевдонимы друг для друга и работают одинаково хорошо в любом контексте.

Когда новая строка добавляется в таблицу SQLite, ROWID может быть определен как часть оператора INSERT или это может быть назначено автоматически ядром базы данных. Чтобы определить ROWID вручную, просто включайте его в список значений, которые будут вставлены. Например:

CREATE TABLE test1(a INT, b TEXT);
INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');

Если ROWID не задан при вставке или если у указанного ROWID есть значение NULL, то соответствующий ROWID создается автоматически. Обычный алгоритм должен дать недавно созданной строке ROWID, который является на единицу больше, чем самый большой ROWID в таблице до вставки. Если таблица первоначально пуста, то используется ROWID 1. Если самый большой ROWID равен самому большому целому числу (9223372036854775807), тогда ядро базы данных начинает выбирать кандидата ROWID наугад, пока не находит тот, который ранее не используется. Если никакой неиспользованный ROWID не может быть найден после разумного количества попыток, операция по вставке терпит неудачу с ошибкой SQLITE_FULL. Если никакие отрицательные значения ROWID не будут вставлены явно, то автоматически произведенные значения ROWID всегда будут больше, чем ноль.

Нормальный алгоритм выбора ROWID, описанный выше, произведет монотонно увеличивающийся уникальный ROWID, пока вы никогда не используете максимальное значение ROWID и вы никогда не удаляете запись в таблице с самым большим ROWID. Если вы когда-нибудь удаляете строки или если вы когда-нибудь создаете строку с максимальным возможным ROWID, то ROWID от ранее удаленных строк мог бы быть снова использован, создавая новые строки и недавно созданные ROWID не могут быть в строгом порядке по возрастанию.

3. Ключевое слово AUTOINCREMENT

Если у колонки есть тип INTEGER PRIMARY KEY AUTOINCREMENT, тогда используется, немного отличающийся алгоритм выбора ROWID. ROWID, выбранный для новой строки, является по крайней мере на единицу больше, чем самый большой ROWID, который когда-либо прежде существовал в этой таблице. Если таблица прежде никогда не содержала данных, то используется ROWID 1. Если самый большой ROWID был ранее вставлен, то новый INSERT не позволен, и любая попытка вставить новую строку потерпит неудачу с ошибкой SQLITE_FULL. Только значения ROWID от предыдущих транзакций, которые были переданы, рассматриваются. Значения ROWID, которые были отменены, проигнорированы и могут быть снова использованы.

SQLite отслеживает самый большой ROWID с использованием внутренней таблицы "sqlite_sequence". Таблица sqlite_sequence составлена и инициализирована автоматически каждый раз, когда нормальная таблица, которая содержит AUTOINCREMENT создана. Содержание таблицы sqlite_sequence может быть изменено, используя обычные операторы UPDATE, INSERT и DELETE. Но создание модификаций к этой таблице, вероятно, встревожит алгоритм генерации ключей AUTOINCREMENT. Удостоверьтесь, что вы знаете, что вы делаете, прежде чем вы предпримете такие изменения. Таблица sqlite_sequence не отслеживает изменения ROWID, связанные с запросом UPDATE, только с INSERT.

Поведение, осуществленное ключевым словом AUTOINCREMENT, тонко отличается от поведения по умолчанию. С AUTOINCREMENT у строк с автоматически отобранным ROWID, как гарантируют, будут ROWID, которые никогда не использовались прежде этой таблицей в той же самой базе данных. И автоматически произведенные ROWID монотонно увеличатся. Это важные свойства в определенных ситуациях. Но если для вашего приложения не нужны эти свойства, необходимо, вероятно, остаться с поведением по умолчанию, так как использование AUTOINCREMENT требует, чтобы дополнительная работа была сделана, когда каждая строка вставляется и таким образом заставляет INSERT работать немного медленнее.

Обратите внимание на то, что "монотонно увеличение" не подразумевает, что ROWID всегда увеличивается точно на единицу. Однако, если вставка терпит неудачу из-за (например) ограничения уникальности, ROWID неудавшейся попытки вставки не может быть снова использован на последующих вставках, приводя к промежуткам в последовательности ROWID. AUTOINCREMENT гарантирует, что автоматически выбранный ROWIDs будет увеличиваться, но не что они будут последовательны.

Так как ключевое слово AUTOINCREMENT изменяет поведение алгоритма выбора ROWID, AUTOINCREMENT не позволен в таблицах WITHOUT ROWID или ни на каком столбце таблицы кроме INTEGER PRIMARY KEY. Любая попытка использовать AUTOINCREMENT в таблице WITHOUT ROWID или на колонке кроме INTEGER PRIMARY KEY приведет к ошибке.