Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists
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
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
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
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
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
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
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
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