4 ms·

"SQLite4 requires all elements of the PRIMARY KEY to be non-null. This is an SQL standard. Due to an oversight in early versions, SQLite3 does not enforce the N

by sanxiyn 12y ago

"SQLite4 requires all elements of the PRIMARY KEY to be non-null. This is an SQL standard. Due to an oversight in early versions, SQLite3 does not enforce the NOT NULL constraint on PRIMARY KEY columns since by the time the omission was discovered SQLite3 was in such widespread use, activation of NOT NULL enforcement would have broken too many programs." I find this sad, and I wonder what those too many programs are. Why would one put NULL on PRIMARY KEY?

MaxGabriel 12y ago
It's not super unreasonable for a multi-element primary key, is it? For example, at work we have a MySQL table that stores advertising stats with a (Publisher ID, Country, Day) primary key[1]. All those fields are non-nullable, but I can imagine someone using NULL for the country[2] if we couldn't determine it (it's based on IP). [1] It's slightly more complicated, but it's basically similar. [2] "zz" might be appropriate to represent an unknown country, but other domains might not have a good default value Edit: double asterisks messing up my formatting
- zzzeek 12y ago
  it's unreasonable. NULL isn't a value, it means, "the value can't be known". If a primary key of a table has part of it that is unknown, you've designed your schema completely incorrectly, and you should brush up on normal form: http://en.wikipedia.org/wiki/Database_normalization#Normal_forms http://en.wikipedia.org/wiki/Database_normalization#Normal_f...
skue 12y ago
Hopefully there aren't many people designing data models with NULL primary keys. However, if SQLite never enforced non-NULL behavior then it's certainly possible that along the way some applications could have written an errant record to a database with a NULL primary key (instead of treating it as an error case). And if a "fixed" version of SQLite3 ever shipped, then these previously-valid data files would suddenly become invalid. Imagine being an app developer and having random customers whose data files suddenly break because the OS happened to upgrade the SQLite3 library to a version that included this fix. I completely understand why the the SQLite maintainers may have decided it wasn't worth the hassle to fix things prior to SQLite4.
- furyofantares 12y ago
  > the OS happened to upgrade the SQLite3 library I don't think this actually detracts from your point, but, are there cases where that can actually happen? Isn't SQLite always statically linked with the applicaton?
  - panzi 12y ago
    E.g. on iOS and Android you get access to the system's SQLite library and apparently can't even opt-out (because it is used by system APIs that you will use, if I understand correctly). See: https://news.ycombinator.com/item?id=7756332 https://news.ycombinator.com/item?id=7756332
  - dragonwriter 12y ago
    > Isn't SQLite always statically linked with the applicaton? SQLite can be dynamically linked, though static linking is probably more common.
  - rat87 12y ago
    according to `apt-cache rdepends slite3` on ubuntu 14.04 packages including the following link to sqlite dynamic packages(this may be partially due to debian heavily discouraging statistically linked packages. zabbix-proxy-sqlite3 yokadi uwsgi usermetricsservice trove-common sympa sqliteman sisu-sqlite rt4-db-sqlite roundcube-sqlite3 redmine-sqlite ratbox-services-sqlite postfix-cluebringer-sqlite3 pdns-backend-sqlite3 pbnj opendnssec-enforcer-sqlite3 lire krecipes kamailio-sqlite-modules inspircd imms-common hud-tools gbrowse eurephia dotclear beancounter adminer bacula-director-sqlite3
    - emillon 12y ago
      sqlite3 is the package that provides the command-line tool. A lot more packages depend on the dynamic library: "apt-cache rdepends libsqlite3-0 | wc -l" returns 407 results.
asveikau 12y ago
Thankfully from what I have seen sqlite is maintained with that old-school style of library development, the one you scarcely see these days, and don't hear much about on HN: the one where "thou shalt not break existing code" has some weight. I'd be nervous about depending on a library where the whim of the developer thinking "I find this sad" breaks my program. sqlite4 OTOH seems to be the "let's break things" phase. Probably healthy to have both attitudes at certain stages.
- malkia 12y ago
  Upvoted. SQLITE as an API does so many things right: - Providing it's own malloc/free "proxies" to work away with different linked CRT versions (Microsoft) - Prefixing ("namespacing") everything, and not exporting symbols outside of the API - Configuration of the API before it's started, where it makes sense (threading model for example) - Ability to retrieve compilation options back (e.g. how it was compiled and with what features) - many other things
- swah 12y ago
  Agree with you on the importance of backwards compatibility. But there is also the idea that you should at some point draw the line and break compabitibility so that you can really fix things is also defendable. In my opinion by releasing the library/project under a new name, like python does. The worst and most common though is the "break all things for no reason at all"...
dschiptsov 12y ago
If you would think for a couple of minutes you will [probably] understand that "gaps" (NULLs) in a PRIMARY KEY column is a plain stupidity.

by sanxiyn 12y ago

MaxGabriel 12y ago

It's not super unreasonable for a multi-element primary key, is it? For example, at work we have a MySQL table that stores advertising stats with a (Publisher ID, Country, Day) primary key[1]. All those fields are non-nullable, but I can imagine someone using NULL for the country[2] if we couldn't determine it (it's based on IP). [1] It's slightly more complicated, but it's basically similar. [2] "zz" might be appropriate to represent an unknown country, but other domains might not have a good default value Edit: double asterisks messing up my formatting

zzzeek 12y ago
it's unreasonable. NULL isn't a value, it means, "the value can't be known". If a primary key of a table has part of it that is unknown, you've designed your schema completely incorrectly, and you should brush up on normal form: http://en.wikipedia.org/wiki/Database_normalization#Normal_forms http://en.wikipedia.org/wiki/Database_normalization#Normal_f...

skue 12y ago

Hopefully there aren't many people designing data models with NULL primary keys. However, if SQLite never enforced non-NULL behavior then it's certainly possible that along the way some applications could have written an errant record to a database with a NULL primary key (instead of treating it as an error case). And if a "fixed" version of SQLite3 ever shipped, then these previously-valid data files would suddenly become invalid. Imagine being an app developer and having random customers whose data files suddenly break because the OS happened to upgrade the SQLite3 library to a version that included this fix. I completely understand why the the SQLite maintainers may have decided it wasn't worth the hassle to fix things prior to SQLite4.

furyofantares 12y ago
> the OS happened to upgrade the SQLite3 library I don't think this actually detracts from your point, but, are there cases where that can actually happen? Isn't SQLite always statically linked with the applicaton?
- panzi 12y ago
  E.g. on iOS and Android you get access to the system's SQLite library and apparently can't even opt-out (because it is used by system APIs that you will use, if I understand correctly). See: https://news.ycombinator.com/item?id=7756332 https://news.ycombinator.com/item?id=7756332
- dragonwriter 12y ago
  > Isn't SQLite always statically linked with the applicaton? SQLite can be dynamically linked, though static linking is probably more common.
- rat87 12y ago
  according to `apt-cache rdepends slite3` on ubuntu 14.04 packages including the following link to sqlite dynamic packages(this may be partially due to debian heavily discouraging statistically linked packages. zabbix-proxy-sqlite3 yokadi uwsgi usermetricsservice trove-common sympa sqliteman sisu-sqlite rt4-db-sqlite roundcube-sqlite3 redmine-sqlite ratbox-services-sqlite postfix-cluebringer-sqlite3 pdns-backend-sqlite3 pbnj opendnssec-enforcer-sqlite3 lire krecipes kamailio-sqlite-modules inspircd imms-common hud-tools gbrowse eurephia dotclear beancounter adminer bacula-director-sqlite3
  - emillon 12y ago
    sqlite3 is the package that provides the command-line tool. A lot more packages depend on the dynamic library: "apt-cache rdepends libsqlite3-0 | wc -l" returns 407 results.

asveikau 12y ago

Thankfully from what I have seen sqlite is maintained with that old-school style of library development, the one you scarcely see these days, and don't hear much about on HN: the one where "thou shalt not break existing code" has some weight. I'd be nervous about depending on a library where the whim of the developer thinking "I find this sad" breaks my program. sqlite4 OTOH seems to be the "let's break things" phase. Probably healthy to have both attitudes at certain stages.

malkia 12y ago
Upvoted. SQLITE as an API does so many things right: - Providing it's own malloc/free "proxies" to work away with different linked CRT versions (Microsoft) - Prefixing ("namespacing") everything, and not exporting symbols outside of the API - Configuration of the API before it's started, where it makes sense (threading model for example) - Ability to retrieve compilation options back (e.g. how it was compiled and with what features) - many other things
swah 12y ago
Agree with you on the importance of backwards compatibility. But there is also the idea that you should at some point draw the line and break compabitibility so that you can really fix things is also defendable. In my opinion by releasing the library/project under a new name, like python does. The worst and most common though is the "break all things for no reason at all"...

dschiptsov 12y ago

If you would think for a couple of minutes you will [probably] understand that "gaps" (NULLs) in a PRIMARY KEY column is a plain stupidity.