2016-04-15 1:19 GMT+02:00 J Decker <d3ck0r at gmail.com>:
> I would total expect any column I created without NOT NULL (double
> negative) to allow NULL whether INDEX or UNIQUE or PRIMARY is applied
> additionally....what database does otherwise? MSSQL?
>
> On Thu, Apr 14, 2016 at 2:56 PM, R Smith <rsmith at rsweb.co.za> wrote:
> >
> >
> > On 2016/04/14 10:23 PM, Cecil Westerhof wrote:
> >>
> >> 2016-04-14 22:10 GMT+02:00 Richard Hipp <drh at sqlite.org>:
> >>
> >>> On 4/14/16, Cecil Westerhof <cldwesterhof at gmail.com> wrote:
> >>>>
> >>>> Yes that makes sense. But could not a type of PRAGMA be used? So if
> the
> >>>> PRAGMA is not defined the old functionality and your historical data
> is
> >>>> save. And if the PRAGMA is defined the new functionality.
> >>>>
> >>> Easier: Just declare the columns in question as NOT NULL. Or use a
> >>> WITHOUT ROWID table which *does* enforce NOT NULL.
> >>>
> >> The problem is that you do not expect that values in the primary key can
> >> be NULL, so you do not use NOT NULL. I now know that I need to do this
> and
> >> I do it now, but the unaware will be bitten, just as I was. It was not a
> >> big problem, because I just started playing with SQLite, but when you
> have
> >> a very big database which gets a lot of data every day and a year later
> >> you
> >> find out you are bitten by this ?
> >
> >
> > What makes you "expect" this? Nothing in the SQLite documentation I
> hope.
> > Is it perhaps habit based on how some other DBs do it?
> >
> > How will "those who don't know about NOT NULL" be better serviced by a
> > pragma which they also don't know about?
> > The documentation is very clear on the matter, even a cursory glance
> would
> > educate "the unaware".
> >
> > Making more things with more documentation to read, in order to help
> people
> > with other documentation they didn't read - not a salient course of
> action I
> > think.
>
?Maybe you should read the documentation of SQLite? ;-)
If you go to:
https://www.sqlite.org/lang_createtable.html
You will find:
According to the SQL standard, PRIMARY KEY should always
imply NOT NULL. Unfortunately, due to a bug in some early
versions, this is not the case in SQLite. Unless the column
is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID
table or the column is declared NOT NULL, SQLite allows NULL
values in a PRIMARY KEY column. SQLite could be fixed to
conform to the standard, but doing so might break legacy
applications. Hence, it has been decided to merely document
the fact that SQLite allowing NULLs in most PRIMARY KEY
columns.
And I am not the only one who has fallen for this problem. I found out
because I noticed people complaining about it. Then I checked and found out
that it did not work as expected.
If a car company creates a car where the brakes does not work when you make
a left turn, but they put this in the documentation of the car. Do you
think it is the customers fault if the find out this when they want to
brake while making a left turn?
?I know that I am new here, but I do not think I should be mocked. You make
my suggestion sound needlessly complicated. SQLite could be made in such a
way that when it creates a new database, this is with the pragma. Then
everything works perfect. The old database does not brake, because it does
not have the pragma. And when creating a new database it confirms to the
SQL standard and those not create nasty surprises for the unaware.
--
Cecil Westerhof
