Yesterday I started a new project with SQLite. I wanted to create some simple integer-based primary keys and used an ORM to generate code. I then noticed that the primary keys were generated as Nullable types. I asked on Gitter and someone pointed me to this bit of documentation:
"According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY <https://www.sqlite.org/lang_createtable.html#rowid> or the table is a WITHOUT ROWID <https://www.sqlite.org/withoutrowid.html> table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allowing NULLs in most PRIMARY KEY columns." As I read this, an INTEGER PRIMARY KEY column is always NOT NULL. However, someone then pointed out to me that SQLite doesn't reflect this in the pragma metadata: sqlite> create table test(id integer primary key autoincrement); sqlite> pragma table_info('test'); cid|name|type|notnull|dflt_value|pk 0|id|integer|0||1 sqlite> create table test3 (id integer not null primary key autoincrement); sqlite> pragma table_info('test3'); cid|name|type|notnull|dflt_value|pk 0|id|integer|1||1 If INTEGER PRIMARY KEYs are always NOT NULL, I'd reasonably expect that it will be `notnull` even if NOT NULL is not explicitly specified. Is this a bug? Regards, Dirkjan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
