Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
Thank you all for your answers. I have made the changes necessary. Alex -Message d'origine- De : sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] De la part de Keith Medcalf Envoyé : jeudi 18 juillet 2019 21:11 À : SQLite mailing list Objet : Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ... 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
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 ?
I have tried to read https://sqlite.org/lang_createtable.html#constraints and
https://www.sqlite.org/nulls.html but I don't really have found why I can
insert 2 records that have the same primary key.
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .headers on
sqlite> .mode column
sqlite> .nullvalue null
sqlite>
sqlite> CREATE TABLE "sales" (
...> "client"TEXT,
...> "salesman"TEXT,
...> "revenue"REAL,
...> PRIMARY KEY("client","salesman")
...> );
sqlite>
sqlite>
sqlite>
sqlite> INSERT INTO sales ("client", "salesman", "revenue")
...> VALUES ('C1', NULL, 10.0);
sqlite>
sqlite> INSERT INTO sales ("client", "salesman", "revenue")
...> VALUES ('C1', NULL, 10.0);
sqlite>
sqlite> INSERT INTO sales ("client", "salesman", "revenue")
...> VALUES ('C1', '', 10.0);
sqlite>
sqlite> INSERT INTO sales ("client", "salesman", "revenue")
...> VALUES ('C1', '', 10.0);
Error: UNIQUE constraint failed: sales.client, sales.salesman
sqlite>
sqlite> SELECT * FROM sales;
client salesmanrevenue
-- -- --
C1 null10.0
C1 null10.0
C1 10.0
sqlite>
Thanks in advance for your insights.
Alex
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] printf() - Local decilmal separator
Hello,
1st question : Is there a way to tell printf() to display the decimal separator
set in the OS ?
For example, the decimal separator in my OS is set to comma (,), I would like
printf() to display the comma as the decimal separator.
sqlite> select printf('%.2f %%', 25.365419);
25.37 %
2nd question : Is there a way that printf displays thousand separators and
decimal separators ?
sqlite> select printf('%,.2f %%', 25566425.365419);
25566425.37 %
sqlite> select printf('%,f %%', 25566425.365419);
25566425.365419 %
sqlite> select printf('%,d %%', 25566425.365419);
25,566,425 %
I haven't found anything here : https://www.sqlite.org/printf.html
Best Regards
Alex
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Window functions in System.Data.Sqlite
Hello, I am sorry in advance for this post but I am really looking forward having the window functions in System.Data.Sqlite. The expected release date went from December 2018 to February 2019 in the news page. Do you have any more info when will the new version of System.Data.Sqlite be released ? Thank you. Alex ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
