I believe that when NULLs are allowed as PKs, they are all distinct. So, you can multiple rows with a NULL value as the PK.
-----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Dominique Devienne Sent: Thursday, December 11, 2014 11:27 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Select count(*) On Thu, Dec 11, 2014 at 5:03 PM, Gabriel Corneanu <[email protected] > wrote: > I asked a similar question some time ago... > See here: > https://www.mail-archive.com/[email protected]/msg77488.html >> not a problem for rowid/pk (which are not allowed to be NULL), but it >> matters a lot in the general case. > DRH write: > PRIMARY KEYs (except for INTEGER PRIMARY KEYs) are allowed to be NULL > in SQLite. This goes back to a bug in the code from many years ago. > By the time the bug was discovered, SQLite was already in wide-spread > use and so the decision was made to not fix the bug since doing so > would cause > compatibility problems. it's a bit sad to carry "fundamental" baggage like, in all cases. I can see why it would stay the default, but surely a pragma could remedy the situation to get the best possible conformance? I know this was discussed before on list, but I for one would welcome this new pragma. To come back on the subject, even if SQLite allows a null PK, there (hopefully) can be a single row using it, and it (this null row) is still indexed, no? So why couldn't sqlite using the PK index to reduce the IO when doing a "select count(*) from t_with_non_int_pk", to avoid scanning the table? Perhaps it's a naive question, but I don't see why ATM, and would welcome an explanation. Thanks, --DD _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
