Re: [sqlite] NOT NULL integer primary key

[James K. Lowden]

On Fri, 26 May 2017 18:04:14 +0200 (CEST)
Eric  wrote:

> Why should the INSERT return an error? It is quite OK to, when
> inserting a row, not specify a value for a NOT NULL column - as long
> as the DDL has specified some way of constructing a value. 

The SQL in question is

>>> insert into test values (null, 'row1');

There's a difference between not supplying a value, and specifying
NULL: 

insert into test values (null, 'row1');
insert into test (data) values ('row1');

Specifying NULL for a non-null column is an error in every SQL DBMS with
which I'm familar, whether or not a default is defined.  

> > It's a documented feature, so it's not a bug.  But it is decidedly
> > odd.  
> 
> The DDL specifies, in SQLite's own unique way, that a value will be
> provided, and the application author knows that a value will be
> provided, so where on earth is the problem? I don't think it's
> peculiar at all

It's peculiar because it doesn't say what it does.  It's nonstandard,
and nothing in the text of the DDL suggests a default exists.  SQL-92
includes syntax to define a default; it uses the word DEFAULT.  

It's a problem because the behavior is in exception to what the
standard specifies, the opposite of what is plainly expressed, and
contrary to what any normal SQL DBMS does. The SQLite user has to be
aware that non-NULL primary key "integer" columns have a special,
implicit property: that NULL is accepted on INSERT, and converted to an
indeterminate value.  

Put simply, how does the syntax 

id integer not null primary key

suggest that an inserted NULL will be converted to a value?  

If a magic value generated silently for a primary key isn't peculiar
enough, consider that it behaves differently on INSERT and UPDATE.
Try to set the same column to NULL with INSERT, get a value; with
UPDATE, it's an error. How many datatypes have that property? One.  

> the application author knows

As I said, it's not a bug because it's well documented.  But if you
haven't studied the SQLite documentation in detail, you might be
forgiven for thinking that INT PRIMARY KEY and INTEGER PRIMARY KEY mean
the same thing.  I'm not so sure every application author knows, but
it's a safe bet many find out the hard way.  

--jkl




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOT NULL integer primary key

[Eric]

On Thu, 25 May 2017 21:47:20 -0400, "James K. Lowden" 
 wrote:
> On Fri, 19 May 2017 12:47:32 -0600
> "Keith Medcalf"  wrote:
>>On Thursday, 18 May, 2017 10:17, Paul Sanderson 
>> wrote:
>>> Create table test (id integer not null primary key, data text);
>>> insert into test values (null, 'row1');
>>> select * from test;
>>> 1, row1
> 
>> Specifying NOT NULL on an INTEGER PRIMARY KEY (which is an alias for
>> the RowID) is a redundant redundancy.  The RowID cannot be null and
>> must have a value.  

What on earth is a "redundant redundancy"? Never mind, it's irrelevant.

> Maybe, but despite saying it twice, the INSERT succeeded, when it
> should return an error.  

"INTEGER PRIMARY KEY NOT NULL" is valid standard SQL syntax, and it is OK
to specify "NOT NULL", if only because the primary key specification might
be lower down the statement, as a table constraint or even (though not in
SQLite) in a subsequent separate statement. This gives the DBMS a chance
to do a consistency check for us, in case we said something equivalent to
"INTEGER PRIMARY KEY NULL" (actually SQLite lets us get away with that).

Why should the INSERT return an error? It is quite OK to, when inserting
a row, not specify a value for a NOT NULL column - as long as the DDL has
specified some way of constructing a value. The usual thing is the DEFAULT
clause, SQL Server has IDENTITY, PostgreSQL has SERIAL ... . The point
is that the DBMS will provide a value, and the NOT NULL will thereby
be obeyed.

> 
>> but should the not null constraint be obeyed?
> 
>> Trying to insert a null value is how you get one generated for you.  
> 
> I just want to point out how peculiar that is.  This is the test:
> 
> 1.  The datatype must be integer
> 2.  The column must be in the primary key
> 3.  The DBMS must be SQLite
> 
> If the above are all true, then, and only then, the INSERT "succeeds"
> in the sense that the data inserted into the database are neither what
> the application supplied, nor what is expressed in the DDL.  
> 
> It's a documented feature, so it's not a bug.  But it is decidedly
> odd.  

The DDL specifies, in SQLite's own unique way, that a value will be
provided, and the application author knows that a value will be provided,
so where on earth is the problem? I don't think it's peculiar at all, not
even SQLite's choice of syntax, which is just a duck choice - the RowID
(an IMPLEMENTATION detail) looks just like an auto-increment integer, and
behaves like an integer primary key, so if you want an auto-increment PK,
overload the syntax to declare it and overload the implementation detail
to make it work.

Eric
-- 
ms fnd in a lbry
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOT NULL integer primary key

[Keith Medcalf]

On Thursday, 18 May, 2017 10:17, Paul Sanderson  
wrote:

> Create table test (id integer not null primary key, data text);
> insert into test values (null, 'row1');
> select * from test;
> 1, row1
 
> I know that if you provide a NULL value to a column define as integer
> primary key that SQLite will provide a rowid, but should the not null
> constraint be obeyed?

Specifying NOT NULL on an INTEGER PRIMARY KEY (which is an alias for the RowID) 
is a redundant redundancy.  The RowID cannot be null and must have a value.  
Trying to insert a null value is how you get one generated for you.  

It should probably be an error to specify "NULL" or "NOT NULL" (or anything 
else other than perhaps AUTOINCREMENT) with an INTEGER PRIMARY KEY (RowID 
alias).  However, rather than do so the redundant redundancies and the 
illogical declarations (such as INTEGER PRIMARY KEY NULL or COLLATE clauses) 
are merely silently ignored.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOT NULL integer primary key

[Paul Sanderson]

Ahh being dull and in a hurry
thanks


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 18 May 2017 at 17:26, Gwendal Roué  wrote:

>
> > Le 18 mai 2017 à 18:16, Paul Sanderson  a
> écrit :
> >
> > Is this a bug?
> >
> > Create table test (id integer not null primary key, data text);
> > insert into test values (null, 'row1');
> > select * from test;
> > 1, row1
> >
> > I know that if you provide a NULL value to a column define as integer
> > primary key that SQLite will provide a rowid, but should the not null
> > constraint be obeyed?
>
> Hello Paul,
>
> The constraint is obeyed, since there is no NULL values in the database.
>
> To put it in another way: constraints are properties of the *database
> content*, not of the *operations* on content. They're static, not dynamic.
>
> That's why constraints can be checked with PRAGMA
> schema.foreign_key_check, which tells if the current state of the database
> content is valid.
>
> That's also why the insert statement above succeeds, as long as the value
> that is eventually inserted in the database is NOT NULL.
>
> Gwendal Roué
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOT NULL integer primary key

[Gwendal Roué]

> Le 18 mai 2017 à 18:16, Paul Sanderson  a écrit 
> :
> 
> Is this a bug?
> 
> Create table test (id integer not null primary key, data text);
> insert into test values (null, 'row1');
> select * from test;
> 1, row1
> 
> I know that if you provide a NULL value to a column define as integer
> primary key that SQLite will provide a rowid, but should the not null
> constraint be obeyed?

Hello Paul,

The constraint is obeyed, since there is no NULL values in the database.

To put it in another way: constraints are properties of the *database content*, 
not of the *operations* on content. They're static, not dynamic.

That's why constraints can be checked with PRAGMA schema.foreign_key_check, 
which tells if the current state of the database content is valid.

That's also why the insert statement above succeeds, as long as the value that 
is eventually inserted in the database is NOT NULL.

Gwendal Roué

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users