> Le 18 juil. 2019 à 18:05, J. King <jk...@jkingweb.ca> a écrit : > >> Hello, >> >> I have created a table. >> >> CREATE TABLE "sales" ( >> "client" TEXT, >> "salesman" TEXT, >> "revenue" REAL, >> PRIMARY KEY("client","salesman") >> ); >> >> >> I can run the query below mutliple times without any error : >> >> INSERT INTO sales ("client", "salesman", "revenue") >> VALUES ('C1', NULL, 10.0); >> >> >> Have I missed something in the CREATE instruction ? >> Is this a normal behaviour ? >> > It is indeed normal behaviour. See <https://sqlite.org/rowidtable.html> for > an explanation. If you want a true primary key, use a without rowid table: > > CREATE TABLE "sales" ( > "client" TEXT, > "salesman" TEXT, > "revenue" REAL, > PRIMARY KEY("client","salesman") > ) WITHOUT ROWID;
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). https://www.sqlite.org/quirks.html#primary_keys_can_sometimes_contain_nulls Then, now that you know why NULL is accepted (when you rightly would have expected it to be refused), you can insert many times because each NULL is treated distinct in a UNIQUE column. https://www.sqlite.org/nulls.html — Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen, Olivier Mascia _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users