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 salesman revenue
---------- ---------- ----------
C1 null 10.0
C1 null 10.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
