[sqlite] Primary key values can be NULL
"INTEGER PRIMARY KEY" declares an alias for the rowid. It is always NOT NULL (it is the record number) and it is always and integer (it is not ducky-typed). Specifying NULL for the rowid causes the generation of the "next" rowid/record number. AUTOINCREMENT modifies "next" to mean "one greater than any rowid/record number that has ever been used in the table" rather than the normal "one greater than the current max rowid/record number". You CANNOT insert a null into an INTEGER PRIMARY KEY (that is, obtain an actual NULL value in the table for that column), nor can you insert any value that is not an integer (that is, have the value of the rowid/record number for an inserted record be anything other than an integer). > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Adam Devita > Sent: Monday, 18 April, 2016 13:45 > To: SQLite mailing list > Subject: Re: [sqlite] Primary key values can be NULL > > I had a similar thought, until I imagined > > Program uses sqlite DLL > The program creates dbs, and sometimes does inserts with null primary > keys. (Why is beyond the scope, it just does sometimes.) > > Upgrading the DLL would start making files in the new format, but the > program using the dll doesn't know that. It just starts failing. > > regards, > Adam > > On Mon, Apr 18, 2016 at 10:29 AM, David Raymond > wrote: > > I don't mean to poke a busy thread with a possibly stupid newbie > question, but here goes. > > > > How is this that much different than say, the SQLITE_DEFAULT_FILE_FORMAT > compile option? (Pasting it here then continuing comment below) > > > > Text pasted here > > SQLITE_DEFAULT_FILE_FORMAT=<1 or 4> > > > > The default schema format number used by SQLite when creating new > database files is set by this macro. The schema formats are all very > similar. The difference between formats 1 and 4 is that format 4 > understands descending indices and has a tighter encoding for boolean > values. > > > > All versions of SQLite since 3.3.0 (2006-01-10) can read and write > any schema format between 1 and 4. But older versions of SQLite might not > be able to read formats greater than 1. So that older versions of SQLite > will be able to read and write database files created by newer versions of > SQLite, the default schema format was set to 1 for SQLite versions through > 3.7.9 (2011-11-01). Beginning with version 3.7.10, the default schema > format is 4. > > > > The schema format number for a new database can be set at runtime > using the PRAGMA legacy_file_format command. > > End quoted section > > > > The key point when introducing something new seems to be "as long as old > versions will know they shouldn't mess with it, then it's ok." So things > like CTE's can be added to the language as the old parser will gag on them > and not try to do something wrong with them and fail. But just straight up > changing the enforcement here would be bad, because the old version > wouldn't know that something new is going on. So although the above file > format option is intended for the physical structure of the file, could > for example we call file format 5 to be "same format, but will not null > primary key enforced." Then old versions would open it up, see file format > 5, and throw their hands up saying "I can't deal with this." And with new > versions it wouldn't be a changeable option, it would be "hey, once you > create this database file with this option, then you're stuck with that > enforcement forever." Looking at the dates above, format 4 was optional > for 5 years before it got a promotion to default, > si > > milarly a new value would have to be explicitly specified for n years > before anyone would have to worry about there being a "default build" that > would make something that could not be read by old versions. > > > > I know that actually using SQLITE_DEFAULT_FILE_FORMAT for this would be > very bad and not what it's intended for. But for example, there are 20 > bytes of "Reserved for expansion. Must be zero." in the header at the > moment. Do past or current versions throw up an error if those aren't zero > at the moment? Might it be time to appropriate a byte of reserved space > for new flags or an additional number? Or would that be the start of a > slippery slope? > > > > (As a newbie I apologize if this is just plain wrong, if I just created > the sound of hundreds of foreheads smacking into their desks in unison, or > if
[sqlite] Primary key values can be NULL
I don't mean to poke a busy thread with a possibly stupid newbie question, but here goes. How is this that much different than say, the SQLITE_DEFAULT_FILE_FORMAT compile option? (Pasting it here then continuing comment below) Text pasted here SQLITE_DEFAULT_FILE_FORMAT=<1 or 4> The default schema format number used by SQLite when creating new database files is set by this macro. The schema formats are all very similar. The difference between formats 1 and 4 is that format 4 understands descending indices and has a tighter encoding for boolean values. All versions of SQLite since 3.3.0 (2006-01-10) can read and write any schema format between 1 and 4. But older versions of SQLite might not be able to read formats greater than 1. So that older versions of SQLite will be able to read and write database files created by newer versions of SQLite, the default schema format was set to 1 for SQLite versions through 3.7.9 (2011-11-01). Beginning with version 3.7.10, the default schema format is 4. The schema format number for a new database can be set at runtime using the PRAGMA legacy_file_format command. End quoted section The key point when introducing something new seems to be "as long as old versions will know they shouldn't mess with it, then it's ok." So things like CTE's can be added to the language as the old parser will gag on them and not try to do something wrong with them and fail. But just straight up changing the enforcement here would be bad, because the old version wouldn't know that something new is going on. So although the above file format option is intended for the physical structure of the file, could for example we call file format 5 to be "same format, but will not null primary key enforced." Then old versions would open it up, see file format 5, and throw their hands up saying "I can't deal with this." And with new versions it wouldn't be a changeable option, it would be "hey, once you create this database file with this option, then you're stuck with that enforcement forever." Looking at the dates above, format 4 was optional for 5 years before it got a promotion to default, similarly a new value would have to be explicitly specified for n years before anyone would have to worry about there being a "default build" that would make something that could not be read by old versions. I know that actually using SQLITE_DEFAULT_FILE_FORMAT for this would be very bad and not what it's intended for. But for example, there are 20 bytes of "Reserved for expansion. Must be zero." in the header at the moment. Do past or current versions throw up an error if those aren't zero at the moment? Might it be time to appropriate a byte of reserved space for new flags or an additional number? Or would that be the start of a slippery slope? (As a newbie I apologize if this is just plain wrong, if I just created the sound of hundreds of foreheads smacking into their desks in unison, or if I just re-stirred a hornets' nest)
[sqlite] Primary key values can be NULL
I had a similar thought, until I imagined Program uses sqlite DLL The program creates dbs, and sometimes does inserts with null primary keys. (Why is beyond the scope, it just does sometimes.) Upgrading the DLL would start making files in the new format, but the program using the dll doesn't know that. It just starts failing. regards, Adam On Mon, Apr 18, 2016 at 10:29 AM, David Raymond wrote: > I don't mean to poke a busy thread with a possibly stupid newbie question, > but here goes. > > How is this that much different than say, the SQLITE_DEFAULT_FILE_FORMAT > compile option? (Pasting it here then continuing comment below) > > Text pasted here > SQLITE_DEFAULT_FILE_FORMAT=<1 or 4> > > The default schema format number used by SQLite when creating new > database files is set by this macro. The schema formats are all very similar. > The difference between formats 1 and 4 is that format 4 understands > descending indices and has a tighter encoding for boolean values. > > All versions of SQLite since 3.3.0 (2006-01-10) can read and write any > schema format between 1 and 4. But older versions of SQLite might not be able > to read formats greater than 1. So that older versions of SQLite will be able > to read and write database files created by newer versions of SQLite, the > default schema format was set to 1 for SQLite versions through 3.7.9 > (2011-11-01). Beginning with version 3.7.10, the default schema format is 4. > > The schema format number for a new database can be set at runtime using > the PRAGMA legacy_file_format command. > End quoted section > > The key point when introducing something new seems to be "as long as old > versions will know they shouldn't mess with it, then it's ok." So things like > CTE's can be added to the language as the old parser will gag on them and not > try to do something wrong with them and fail. But just straight up changing > the enforcement here would be bad, because the old version wouldn't know that > something new is going on. So although the above file format option is > intended for the physical structure of the file, could for example we call > file format 5 to be "same format, but will not null primary key enforced." > Then old versions would open it up, see file format 5, and throw their hands > up saying "I can't deal with this." And with new versions it wouldn't be a > changeable option, it would be "hey, once you create this database file with > this option, then you're stuck with that enforcement forever." Looking at the > dates above, format 4 was optional for 5 years before it got a promotion to > default, si > milarly a new value would have to be explicitly specified for n years before > anyone would have to worry about there being a "default build" that would > make something that could not be read by old versions. > > I know that actually using SQLITE_DEFAULT_FILE_FORMAT for this would be very > bad and not what it's intended for. But for example, there are 20 bytes of > "Reserved for expansion. Must be zero." in the header at the moment. Do past > or current versions throw up an error if those aren't zero at the moment? > Might it be time to appropriate a byte of reserved space for new flags or an > additional number? Or would that be the start of a slippery slope? > > (As a newbie I apologize if this is just plain wrong, if I just created the > sound of hundreds of foreheads smacking into their desks in unison, or if I > just re-stirred a hornets' nest) > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1
[sqlite] Primary key values can be NULL
2016-04-15 22:36 GMT+02:00 Cecil Westerhof : > ?I use it for logging. It is much easier to find something, or delete the > parts you do not need anymore. > An example: https://www.linkedin.com/pulse/use-bash-store-disc-info-sqlite-cecil-westerhof If I could do things better: let me know. -- Cecil Westerhof
[sqlite] Primary key values can be NULL
2016-04-15 22:04 GMT+02:00 R Smith : ?I do not think it is. When you add something to the database to signify >> that a primary key is not allowed to be NULL, then this is not in an old >> database, ergo in the old database NULLs are allowed. Where does backward >> compatibility get broken? As I see it, it is as with partial indexes. > > > > ? >> >>> B. Your suggestion would break backward compatibility, no matter how >>> "light" you coat it. >>> >>> ?I really do not see this. Could you expand on that? >>> >> > Imagine a program written 2 years ago, for instance works on all Apple > computers, or perhaps Android phones. In fact, imagine several programs > where the programmers used, either through conscious decision to employ a > "feature" of SQLite, or perhaps simply out of ignorance, the NULL values > allowed in PK situation. > Some time later, SQLite gets updated with your requested new default. > These programs get re-compiled with defaults, or even just use the packaged > SQLite that are now updated inside OSX or Android, etc. Suddenly, their > programs do no longer work, Keys fail to get inserted... Users have devices > crashing everywhere. Apple perhaps quickly rolls back to a previous version > that was not so damaged, but every compiled-in version of the SQLite code > is out in the wild causing problems. SQLite runs on billions of devices and > systems. > ?It is clear as daylight now. Thank you for the explanation. I hope that I was not to pesky.? > This is what backwards-compatible means, that a system and data will still > work as it always worked, even after you upgrade the engine. To get to your > example of Partial indices - if a DB did not use them before, then it still > doesn't use them, all is well. Only new DB's could use them. > ?Yeah, I did not think it through enough. Luckily the maintainers think better about consequences as I did.? > So if you opt for a pragma that lets you avoid NULLs once you activate > it... sure, but who will that really help? People will need to read the > documentation to even know that pragma exists (which you pointed out they > don't usually do in the first place), and simply /knowing/ the reason for > that pragma, will obviate the need for it. > ?It does not add much then no. The only thing is that people could keep database definitions ?the same? for different databases.? ?The only change I would like is in the documentation. It should be ?impossible? to start using SQLite without knowing this pitfall. Not for me, I know it now, but for future users. ? > I am hoping that is as clear as possible with no hint of mocking - I > honestly mean it well. ?Yes, I now understand it. Next time I should curb my enthusiasm. ;-) C. The suggested work-around would introduce more complication than it is >>> solving. >>> >>> ?I do not see that either. Could you enlighten me? >> > > I trust this point was made above too. > ?Certainly.? ? > ?I like SQLite very much also. I even gave a presentation about it on >> T-DOSE. As you can see from the plethora of questions I ask(ed) I want to >> get serious with it. I do not use MySQL anymore and plan to migrate what I >> still have in H2 to SQLite also. :-) I do not say there is never a reason >> for another database, but I think that in my case there is not (at this >> moment of time). >> > > That's great news :) > Let me just note that we do not really shun the likes of Postgress, MSSQL, > MySQL etc. - those systems answer a different need. ?Me neither, but when SQLite is enough why add the complications of the other type of database? At my work they use DB2: I do not think SQLite would be a good replacement there. :-D ? ?I use it for logging. It is much easier to find something, or delete the parts you do not need anymore. Thanks for the patience. -- Cecil Westerhof
[sqlite] Primary key values can be NULL
On 2016/04/15 8:00 PM, Cecil Westerhof wrote: > 2016-04-15 16:43 GMT+02:00 R Smith : > > > ?Do you want me to tell you that you always read all the documentation of > all the software before you use it? I surely do not. And I think most > people do not. To be honest I think you do also not: it is nearly > impossible. I do want to tell you that, yes. it is not impossible. Maybe not when I am simply the user of some software, but very definitely when I am the programmer and my systems depend on a piece of software from a 3rd party, then I read every bit of documentation, more than once - and I am not special, I am sure that goes for most developers here, but perhaps not all. As for interpreting the documentation wrong, well, that I can do just as well as the next guy! - all the more reason to read it again. > When reading what the documentation says, I interpret it as: we really > would like to comply to the standard. But we cannot. Yes, that is exactly what it says. > I (think I) gave a way to give the maintainers what they want. If there is > a reason that what I suggest will not work, I do not mind to hear it. I'll try explain it more simply below. > > just saying (in this case) you should have read the documentation, I find > not helpful and to be honest a little cheap. Especially because the fix is > mostly for new users. I know what to do now. I just want to make sure that > SQLite gives the least surprise. > https://en.wikipedia.org/wiki/Principle_of_least_astonishment It's not cheap - it's also not how I meant it, but I'll take your point and rest here. > ?I do not think it is. When you add something to the database to > signify that a primary key is not allowed to be NULL, then this is not > in an old database, ergo in the old database NULLs are allowed. Where > does backward compatibility get broken? As I see it, it is as with > partial indexes. > ? >> B. Your suggestion would break backward compatibility, no matter how >> "light" you coat it. >> >> ?I really do not see this. Could you expand on that? Imagine a program written 2 years ago, for instance works on all Apple computers, or perhaps Android phones. In fact, imagine several programs where the programmers used, either through conscious decision to employ a "feature" of SQLite, or perhaps simply out of ignorance, the NULL values allowed in PK situation. Some time later, SQLite gets updated with your requested new default. These programs get re-compiled with defaults, or even just use the packaged SQLite that are now updated inside OSX or Android, etc. Suddenly, their programs do no longer work, Keys fail to get inserted... Users have devices crashing everywhere. Apple perhaps quickly rolls back to a previous version that was not so damaged, but every compiled-in version of the SQLite code is out in the wild causing problems. SQLite runs on billions of devices and systems. This is what backwards-compatible means, that a system and data will still work as it always worked, even after you upgrade the engine. To get to your example of Partial indices - if a DB did not use them before, then it still doesn't use them, all is well. Only new DB's could use them. So if you opt for a pragma that lets you avoid NULLs once you activate it... sure, but who will that really help? People will need to read the documentation to even know that pragma exists (which you pointed out they don't usually do in the first place), and simply /knowing/ the reason for that pragma, will obviate the need for it. I am hoping that is as clear as possible with no hint of mocking - I honestly mean it well. >> C. The suggested work-around would introduce more complication than it is >> solving. >> > ?I do not see that either. Could you enlighten me? I trust this point was made above too. ? > ?I like SQLite very much also. I even gave a presentation about it on > T-DOSE. As you can see from the plethora of questions I ask(ed) I want > to get serious with it. I do not use MySQL anymore and plan to migrate > what I still have in H2 to SQLite also. :-) I do not say there is > never a reason for another database, but I think that in my case there > is not (at this moment of time). That's great news :) Let me just note that we do not really shun the likes of Postgress, MSSQL, MySQL etc. - those systems answer a different need. If you for instance want to store secret information with per-table user access controls, or run a networked client-server kind of data model, then you should not use SQLite, you should really use one of those. SQLite is much better for local storage though, and even as a software data storage for your own software - as Richard delightfully likes to point out (I'm paraphrasing a bit since I cannot recall the actual quote) - "We'd like you to not think of SQLite as a replacement for Postgress/MySQL/etc We'd like you to think of it as a replacement for fOpen()." Cheers, Ryan
[sqlite] Primary key values can be NULL
2016-04-15 20:10 GMT+02:00 John McKown : > On Fri, Apr 15, 2016 at 1:00 PM, Cecil Westerhof > wrote: > ?? > > > > > > ?I do not think it is. When you add something to the database to signify > > that a primary key is not allowed to be NULL, then this is not in an old > > database, ergo in the old database NULLs are allowed. Where does backward > > compatibility get broken? > > > > ?I am somewhat hesitant to ?join in to this, however briefly. What occurs > to me on the breaking of backward in compatibility is an old application, > which is dependent on NULLs in a primary key, creating a _new_ database. > Perhaps because it has a "unload" and "reload" or "import" capability. Or > even one which depends on the user using the sqlite3 command to do backups. > If a PRAGMA were to be established as you have suggested, then it needs to > default to the _old_ way of doing things simply because the aforementioned > old application will not know of it and thus not use it. > ?That would make sense. It looks strange to me to depend on NULLs in a primary key, but you never know. When the default is the old way, then the pragma would not be very beneficial in my opinion. So I think we can take the pragma from the table. On the other hand it could be handy that by using the pragma there is no need for ?unnecessary? NOT NULLs, but that becomes a lot less interesting as what I first thought. The next best option is then to make the problem very clear, so people will not be bitten. -- Cecil Westerhof
[sqlite] Primary key values can be NULL
2016-04-15 16:43 GMT+02:00 R Smith : > > > On 2016/04/15 2:09 PM, Cecil Westerhof wrote: > >> 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. > ?Do you want me to tell you that you always read all the documentation of all the software before you use it? I surely do not. And I think most people do not. To be honest I think you do also not: it is nearly impossible. When reading what the documentation says, I interpret it as: we really would like to comply to the standard. But we cannot. I (think I) gave a way to give the maintainers what they want. If there is a reason that what I suggest will not work, I do not mind to hear it. But just saying (in this case) you should have read the documentation, I find not helpful and to be honest a little cheap. Especially because the fix is mostly for new users. I know what to do now. I just want to make sure that SQLite gives the least surprise. https://en.wikipedia.org/wiki/Principle_of_least_astonishment > 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 do not think it is. When you add something to the database to signify that a primary key is not allowed to be NULL, then this is not in an old database, ergo in the old database NULLs are allowed. Where does backward compatibility get broken? As I see it, it is as with partial indexes. That is a big change (I think), but it did not break backward compatibility. ?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. ?Thank you for clarifying that up. ? > 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
[sqlite] Primary key values can be NULL
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.
[sqlite] Primary key values can be NULL
On Fri, Apr 15, 2016 at 3:04 PM, R Smith wrote: > > That's great news :) > Let me just note that we do not really shun the likes of Postgress, MSSQL, > MySQL etc. - those systems answer a different need. If you for instance > want to store secret information with per-table user access controls, or > run a networked client-server kind of data model, then you should not use > SQLite, you should really use one of those. SQLite is much better for local > storage though, and even as a software data storage for your own software - > as Richard delightfully likes to point out (I'm paraphrasing a bit since I > cannot recall the actual quote) - "We'd like you to not think of SQLite as > a replacement for Postgress/MySQL/etc We'd like you to think of it as > a replacement for fOpen()." > I am not really a developer. More of a dilettante programmer. ?But this last is how I think of ?using SQLite. I use for permanent storage, in preference to reading regular file and putting the data into something like a Java Hashmap. > > > Cheers, > Ryan > -- "He must have a Teflon brain -- nothing sticks to it" Phyllis Diller Maranatha! <>< John McKown
[sqlite] Primary key values can be NULL
2016-04-15 1:19 GMT+02:00 J Decker : > I would total expect any column I created without NOT NULL (double > negative) to allow NULL whether INDEX or UNIQUE or PRIMARY is applied > additionallywhat database does otherwise? MSSQL? > ?Every database that is not SQLite, because it is the standard.? -- Cecil Westerhof
[sqlite] Primary key values can be NULL
2016-04-15 1:19 GMT+02:00 J Decker : > I would total expect any column I created without NOT NULL (double > negative) to allow NULL whether INDEX or UNIQUE or PRIMARY is applied > additionallywhat database does otherwise? MSSQL? > > On Thu, Apr 14, 2016 at 2:56 PM, R Smith wrote: > > > > > > On 2016/04/14 10:23 PM, Cecil Westerhof wrote: > >> > >> 2016-04-14 22:10 GMT+02:00 Richard Hipp : > >> > >>> On 4/14/16, Cecil Westerhof 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
[sqlite] Primary key values can be NULL
On Fri, Apr 15, 2016 at 1:00 PM, Cecil Westerhof wrote: ?? > > ?I do not think it is. When you add something to the database to signify > that a primary key is not allowed to be NULL, then this is not in an old > database, ergo in the old database NULLs are allowed. Where does backward > compatibility get broken? > ?I am somewhat hesitant to ?join in to this, however briefly. What occurs to me on the breaking of backward in compatibility is an old application, which is dependent on NULLs in a primary key, creating a _new_ database. Perhaps because it has a "unload" and "reload" or "import" capability. Or even one which depends on the user using the sqlite3 command to do backups. If a PRAGMA were to be established as you have suggested, then it needs to default to the _old_ way of doing things simply because the aforementioned old application will not know of it and thus not use it. > As I see it, it is as with partial indexes. That is a big change (I think), > but it did not break backward compatibility. > > -- "He must have a Teflon brain -- nothing sticks to it" Phyllis Diller Maranatha! <>< John McKown
[sqlite] Primary key values can be NULL
On Fri, 15 Apr 2016 14:13:12 +0200 Cecil Westerhof wrote: > 2016-04-15 1:19 GMT+02:00 J Decker : > > > I would total expect any column I created without NOT NULL (double > > negative) to allow NULL whether INDEX or UNIQUE or PRIMARY is > > applied additionallywhat database does otherwise? MSSQL? > > > > ?Every database that is not SQLite, because it is the standard.? Last I checked, Microsoft SQL Server raised an error if you declared a table to have a primary key with a NULL column. Whether NULL or NOT NULL was the default depended on database and connection options, but if the effect was to use a NULL column in a primary key, it failed. I stopped worrying about the default a long time ago. I figure every time I type NOT NULL, an angel gets its wings. --jkl
[sqlite] Primary key values can be NULL
On 2016/04/14 10:23 PM, Cecil Westerhof wrote: > 2016-04-14 22:10 GMT+02:00 Richard Hipp : > >> On 4/14/16, Cecil Westerhof 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. Cheers, Ryan
[sqlite] Primary key values can be NULL
2016-04-14 22:10 GMT+02:00 Richard Hipp : > On 4/14/16, Cecil Westerhof 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 ? -- Cecil Westerhof
[sqlite] Primary key values can be NULL
2016-04-14 21:46 GMT+02:00 Richard Hipp : > On 4/14/16, Cecil Westerhof wrote: > > For example a database with partial tables can not be read with a > > SQLite before 3.8.0. So why is SQLite not changed so primary keys can not > > have the value NULL? > > You fail to distinguish between "forwards compatible" and "backwards > compatible". > > Upgrading your program from version X to version Y (with Y>X) should > never cause your historical data to become unreadable. That's what > "backwards compatible" means. Upgrading does not break stuff. SQLite > is backwards compatible. > ?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. -- Cecil Westerhof
[sqlite] Primary key values can be NULL
I was bitten by the fact that values of a primary key can be NULL. I understood that this is for historical reasons. But there has been a lot of changes. For example a database with partial tables can not be read with a SQLite before 3.8.0. So why is SQLite not changed so primary keys can not have the value NULL? -- Cecil Westerhof
[sqlite] Primary key values can be NULL
I would total expect any column I created without NOT NULL (double negative) to allow NULL whether INDEX or UNIQUE or PRIMARY is applied additionallywhat database does otherwise? MSSQL? On Thu, Apr 14, 2016 at 2:56 PM, R Smith wrote: > > > On 2016/04/14 10:23 PM, Cecil Westerhof wrote: >> >> 2016-04-14 22:10 GMT+02:00 Richard Hipp : >> >>> On 4/14/16, Cecil Westerhof 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. > > Cheers, > Ryan > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Primary key values can be NULL
On 4/14/16, Cecil Westerhof 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. -- D. Richard Hipp drh at sqlite.org
[sqlite] Primary key values can be NULL
On 4/14/16, Cecil Westerhof wrote: > For example a database with partial tables can not be read with a > SQLite before 3.8.0. So why is SQLite not changed so primary keys can not > have the value NULL? You fail to distinguish between "forwards compatible" and "backwards compatible". Upgrading your program from version X to version Y (with Y>X) should never cause your historical data to become unreadable. That's what "backwards compatible" means. Upgrading does not break stuff. SQLite is backwards compatible. -- D. Richard Hipp drh at sqlite.org
