NULL is a special thing. It's never considered unique.
SQLite version 3.20.0 2017-08-01 13:24:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table TheTable (ID Integer, Info Text, PRIMARY KEY (ID));
sqlite> insert into TheTable (1,"Test");
Error: near "1": syntax error
sqlite> insert into TheTable values (1,"Test");
sqlite> insert into TheTable values (2,"Test2");
sqlite> insert into TheTable values (null,"TestNull1");
sqlite> insert into TheTable values (null,"TestNull2");
sqlite> insert into TheTable values (null,"TestNull3");
sqlite> insert into TheTable values (2,"Test2.1");
Error: UNIQUE constraint failed: TheTable.ID
sqlite> select count(*) from TheTable;
5
sqlite>
On Thu, Jul 18, 2019 at 12:02 PM Alexandre Billon <a.bil...@bert.fr> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
