On Fri, 26 May 2017 18:04:14 +0200 (CEST)
Eric <e...@deptj.eu> 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
