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. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz >Sent: Thursday, 18 July, 2019 12:33 >To: SQLite mailing list >Subject: Re: [sqlite] I can insert multiple rows with the same >primary key when one of the value of the PK is NULL ... > >> You might prefer adding an explicit NOT NULL on both "client" and >"salesman" columns. >> There is an historical reason why SQLite accepts NULL for primary >key column(s). > >Ok, thanks for the hint, I didn't know that either. But it is a very >odd behavior, because PRIMARY KEY per definition doesn't mean >anything else than UNIQUE NOT NULL. > >_______________________________________________ >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
