Ping -- I thought this might be a bug. Can someone confirm or explain why it's not?
Regards, Dirkjan On Tue, Aug 7, 2018 at 7:34 AM Dirkjan Ochtman <dirk...@ochtman.nl> wrote: > 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
