On Thu, 25 May 2017 21:47:20 -0400, "James K. Lowden" 
<jklow...@schemamania.org> wrote:
> On Fri, 19 May 2017 12:47:32 -0600
> "Keith Medcalf" <kmedc...@dessus.com> wrote:
>>On Thursday, 18 May, 2017 10:17, Paul Sanderson 
>><sandersonforens...@gmail.com> 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

Reply via email to