Нет автоинкремента для Integer Первичный ключ в sqlite3

В sqlite3 faq упоминается, что целочисленный первичный ключ, которому подано нулевое значение, будет автоинкремент. Но это не для меня.

чтобы скопировать, таблицу в sqlite3, CREATE TABLE dummy( serial_num INTEGER PRIMARY KEY, name TEXT); и заполнить ее с помощью python,

import sqlite3 as lite
con = lite.connect('some.db')
cur=con.cursor()
data = "someone name"
cur.execute("INSERT INTO dummy VALUES(NULL, ?)", data)
con.commit()

Первый атрибут serial_num отображается пустым, а атрибут name - в порядке. Когда я делаю SELECT serial_num FROM dummy, я просто получаю кучу пробелов. Что я делаю неправильно?

Ответ 1

Это один из особенностей SQLite. Из прекрасного руководства:

Согласно стандарту SQL, PRIMARY KEY всегда должен подразумевать NOT NULL. К сожалению, из-за давнего контроля над кодированием это не относится к SQLite. Если столбец не является INTEGER PRIMARY KEY, SQLite допускает значения NULL в столбце PRIMARY KEY. Мы могли бы изменить SQLite, чтобы он соответствовал стандарту (и мы могли бы сделать это в будущем), но к тому времени, когда был обнаружен контроль, SQLite был в таком широком использовании, что мы боялись разбить устаревший код, если мы исправили проблему.

Документация на INTEGER PRIMARY KEY немного неясна в отношении того, что именно требуется для того, чтобы столбец являлся этим специальным основным ключом INTEGER, который автоматически увеличивается, но реальность что столбец должен быть NOT NULL, если вы хотите использовать значение NULL для обозначения "дать мне следующее значение автоматического увеличения" при вставке:

create table dummy (
    serial_num integer primary key not null,
    name text
);

Если вы оставите not null, вам нужно сделать свои вставки следующим образом:

insert into dummy (name) values (?)

чтобы получить значение автоинкремента для serial_num. В противном случае SQLite не может сказать разницу между значением NULL, которое означает "дать мне следующее значение автоинкремента" и значением NULL "положить значение NULL в serial_num, потому что столбец разрешает NULL".

Ответ 2

Синтаксис вставки, приведенный выше, не работает в отсутствие not null.

Здесь пример - обратите внимание, что поле ID не автоинкрементно, даже если я использую формат вставки, указанный выше.

sqlite> .schema logTable
CREATE TABLE logTable (ID INTEGER PRIMARY_KEY, ts REAL, level TEXT, message TEXT);
sqlite> INSERT into LOGTABLE (ts, level, message) VALUES (111, "autoinc test", "autoinc test");
sqlite> select * from logtable where ts = 111;
|111.0|autoinc test|autoinc test
sqlite>

Он работает с обходным способом NOT NULL.

sqlite> create TABLE logTable (ID INTEGER PRIMARY KEY NOT NULL, ts REAL, level TEXT, message TEXT);
sqlite> INSERT into LOGTABLE (ts, level, message) VALUES (222, "autoinc test", "autoinc test"); 
sqlite> select * from logtable where ts = 222;
1|222.0|autoinc test|autoinc test

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