Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Simon Slavin]

On 19 Jan 2018, at 1:48pm, R Smith  wrote:

> On 2018/01/19 3:36 PM, Simon Slavin wrote:
> 
>> I hope one day to see SQLite4, in which everything done to avoid breaking 
>> backward compatibility is abandoned. 
> 
> That's an impossibility.
> 
> If you mean that SQLite4 itself will not have to honour backwards 
> compatibility to SQLite3, then yes - but if you intended that SQLite4 itself 
> will have a mechanism by which it will not fall prey to the perils of 
> maintaining backwards compatibility to its own generation, then no, that is 
> impossible. It too will have the same struggle.

Oh no, I get that.  SQLite4 will start its own legacy of foibles.  But it would 
be nice to get rid of the "" quoted variable names, the NULL keys, and a few 
other things.

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

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[R Smith]

On 2018/01/19 3:36 PM, Simon Slavin wrote:

On 19 Jan 2018, at 12:43pm, Richard Hipp  wrote:

I hope one day to see SQLite4, in which everything done to avoid 
breaking backward compatibility is abandoned. 


That's an impossibility.

If you mean that SQLite4 itself will not have to honour backwards 
compatibility to SQLite3, then yes - but if you intended that SQLite4 
itself will have a mechanism by which it will not fall prey to the 
perils of maintaining backwards compatibility to its own generation, 
then no, that is impossible. It too will have the same struggle.


One can hope though that, by that time, so many SQL-standard things have 
been fixed that it won't matter much anymore.



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

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Simon Slavin]

On 19 Jan 2018, at 12:43pm, Richard Hipp  wrote:

> Sometimes one has to compromise the simplicity of the
> design, or to document bugs rather than fix them, in order to avoid
> breaking legacy applications.

I hope one day to see SQLite4, in which everything done to avoid breaking 
backward compatibility is abandoned.

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

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Richard Hipp]

On 1/19/18, Shane Dev  wrote:
> I missed that part of the documentation, thanks

I apologize for this goofy exception to the rules.  Maintaining a
widely-used library like SQLite in a way that is backwards compatible
is difficult.  Sometimes one has to compromise the simplicity of the
design, or to document bugs rather than fix them, in order to avoid
breaking legacy applications.  This is such an instance.  There are
others, which you will no doubt encounter from time to time if you use
SQLite intensely.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Keith Medcalf]

A Primary Key cannot be null.  Except in cases of bug-for-bug backwards 
compatibility.  WITHOUT ROWID tables comply with the SQL specification, not the 
bug-for-bug compatibility mode of rowid tables where due to an age old error 
nulls are allowed in primary keys although they should not be.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Shane Dev
>Sent: Friday, 19 January, 2018 01:32
>To: SQLite mailing list
>Subject: [sqlite] "Error: NOT NULL constraint failed ..." when no
>such constraint exists
>
>Hello,
>
>The following SQL works as I expect -
>
>sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
>child));
>sqlite> insert into edges select null, 1;
>sqlite> select * from edges;
>parent  child
>1
>sqlite>
>
>but if I remove the superfluous rowid column from the table
>definition -
>
>sqlite> drop table edges;
>sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
>child)) without rowid;
>sqlite> insert into edges select null, 1;
>Error: NOT NULL constraint failed: edges.parent
>sqlite>
>
>Why do I get this error?
>___
>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] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Shane Dev]

I missed that part of the documentation, thanks

On 19 January 2018 at 07:51, Rowan Worth  wrote:

> https://www.sqlite.org/withoutrowid.html
>
> "NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT ROWID
> table."
>
> It goes on to say that NOT NULL is supposed to be enforced on all PRIMARY
> KEY columns of _every_ table according to the SQL standard, but an early
> version of sqlite included a bug which allowed NULLs and as a result sqlite
> does not enforce this for ROWID tables.
>
> -Rowan
>
> On 19 January 2018 at 14:32, Shane Dev  wrote:
>
> > Hello,
> >
> > The following SQL works as I expect -
> >
> > sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
> > child));
> > sqlite> insert into edges select null, 1;
> > sqlite> select * from edges;
> > parent  child
> > 1
> > sqlite>
> >
> > but if I remove the superfluous rowid column from the table definition -
> >
> > sqlite> drop table edges;
> > sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
> > child)) without rowid;
> > sqlite> insert into edges select null, 1;
> > Error: NOT NULL constraint failed: edges.parent
> > sqlite>
> >
> > Why do I get this error?
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Rowan Worth]

https://www.sqlite.org/withoutrowid.html

"NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT ROWID
table."

It goes on to say that NOT NULL is supposed to be enforced on all PRIMARY
KEY columns of _every_ table according to the SQL standard, but an early
version of sqlite included a bug which allowed NULLs and as a result sqlite
does not enforce this for ROWID tables.

-Rowan

On 19 January 2018 at 14:32, Shane Dev  wrote:

> Hello,
>
> The following SQL works as I expect -
>
> sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
> child));
> sqlite> insert into edges select null, 1;
> sqlite> select * from edges;
> parent  child
> 1
> sqlite>
>
> but if I remove the superfluous rowid column from the table definition -
>
> sqlite> drop table edges;
> sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
> child)) without rowid;
> sqlite> insert into edges select null, 1;
> Error: NOT NULL constraint failed: edges.parent
> sqlite>
>
> Why do I get this error?
> ___
> 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

[sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Shane Dev]

Hello,

The following SQL works as I expect -

sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
child));
sqlite> insert into edges select null, 1;
sqlite> select * from edges;
parent  child
1
sqlite>

but if I remove the superfluous rowid column from the table definition -

sqlite> drop table edges;
sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
child)) without rowid;
sqlite> insert into edges select null, 1;
Error: NOT NULL constraint failed: edges.parent
sqlite>

Why do I get this error?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users