On 2016/04/15 2:09 PM, Cecil Westerhof wrote: > >> 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.
I'm sorry, I think we are talking past each other - the above is exactly my point made. The documentation clearly explains how it works in SQLite, even if different to the other DBs (and yes, sadly the standard[1] too in this case) there is no reason to "expect" behaviour different to what is documented. My point was that your "expectation" was based on your beliefs and not based on what you've read in SQLite documentation, which can be the only real incitement of expectation from any "SQLite" system. There does not exist a single DB engine which implements the SQLite99 (or other) standard to the letter - PostGres and SQLite probably being some of the closest adherents. [1] - Many other DBs also do not hold to the standard 100% here either, some allow NULL for instance, but only one, where SQLite would allow many, etc. > 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? This example is a stick-man argument, brakes are legally required to be in the position they are, but your argument could work if moved sideways somewhat to, for instance, removing the headlight switch and making it automated. This would certainly be unexpected and probably not fall inside any standard, however, if it is documented correctly and fully (perhaps marked in red) and it is working as documented, nobody would have a real cause for dismay. This kind of thing does happen - and yes, I have seen many complaints as you noted, but 10 or even 20 complaints by people who are all not reading the documentation is still hard to hold up as validation for breaking backward compatibility to millions of systems. (And yes, this *is* what you are asking for still). > > ?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. Firstly, I am terribly sorry if my response came across as mocking, this was never my intent and probably only sounds that way cause you are new and have not seen many of my posts. Please allow me to assure you that I would never mock you and in no way thought that your suggestion was mock-worthy. I fully understand your need (as I've said, we've seen it many times before), and I am also not saying that your assertion and request is unfounded. What I /AM/ saying is that, even though it does bite - A. The documentation clearly shows the truth, anyone can read it there. B. Your suggestion would break backward compatibility, no matter how "light" you coat it. C. The suggested work-around would introduce more complication than it is solving. If I thought your suggestions or requests were silly, I would not bother to answer (luckily we have not many of those on this forum). Also, welcome to SQLite, it's one of the best things that exist in this World, and please do post all your questions, even if they do sound silly - chances are others watching the list might learn from the replies - I know I do. :)
