Actually, I take that back...
sqlite> select * from TheTable;
1|Test
2|Test2
3|TestNull1
4|TestNull2
5|TestNull3
So the inserting of NULL in a primary key (Single instance maybe?) will
insert the new rowid.
Try running a SELECT against your table and see what kind of results you're
obtaining.
On Thu, Jul 18, 2019 at 1:13 PM Stephen Chrzanowski <pontia...@gmail.com>
wrote:
> 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
