Imho it would be helpful (especially for newbies that don't know the full history of SQLite) to have a "PRAGMA strict_mode" or similar, to disable all kinds of historical bugs. They might be relevant for existing applications but in no way for newly created ones. Among the things to consider should be:
- PRIMARY KEY = UNIQUE NOT NULL (what I have learned now *g*) - enable FOREIGN KEY constraints (I know there is already a pragma, but imho it should be included) - strict type enforcement - disable the use of double quotes for strings - default to WITHOUT ROWID ...and probably many more I don't know about ;-) ----- Original Message ----- From: Dominique Devienne <ddevie...@gmail.com> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Sent: Friday, July 19, 2019, 10:25:17 Subject: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ... On Thu, Jul 18, 2019 at 9:11 PM Keith Medcalf <kmedc...@dessus.com> wrote: > Except in SQLite where as a documented behavioural anomaly maintained for > backwards compatibility it simply means "UNIQUE" (for ROWID tables). And > UNIQUE indexes may have NULL components. This is because despite your > wishing that your primary key is the primary key, it is not the primary key. > However when WITHOUT ROWID tables were introduced there was no backwards > compatibility issues (they were new after all) then PRIMARY KEY could be > implemented as UNIQUE NOT NULL ... > https://sqlite.org/nulls.html > https://sqlite.org/rowidtable.html > https://sqlite.org/withoutrowid.html > See especially 2 sub 4 in the latter. This whole thread is good material for the new(ish) quirks page IMHO. As Keith points out, most of the material exists in the doc already, but IMHO the quirks page should be the one-stop page to learn about all those historical or by-design particularities of SQLite, with a short explanation and pointers to other doc places with more details. Just my $0.02 of course :) --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
