Pragma notnull for integer primary keys?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Pragma notnull for integer primary keys?

Dirkjan Ochtman
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Pragma notnull for integer primary keys?

Dirkjan Ochtman
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Pragma notnull for integer primary keys?

Richard Hipp-3
In reply to this post by Dirkjan Ochtman
On 8/7/18, Dirkjan Ochtman <[hidden email]> wrote:
>
> 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?

I don't think so.  INTEGER PRIMARY KEYs have another magic property in
that if you insert a NULL into them, they automatically convert that
NULL into a valid integer primary key.  So  the "notnull" column in
PRAGMA table_info does not mean "is this column always non-null when
read" but rather as "is it an error to insert a NULL into this
column".  For ordinary columns that are marked NOT NULL, the answer
will be "yes" according to both meanings.  But for an INTEGER PRIMARY
KEY, the answer according to the second meaning is "no".

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users