"integer primary key"s cannot contain a null since they're an alias for the
rowid. So when you insert a null into them they act similar to autoincrement
and automatically fill it in with an unused id. (Current implementation is 1
more than the _current_ highest rowid. Subject to change)
If you made it just an int primary key it shows what you want.
D:\Programs\PostgreSQL\11\bin>sqlite3
SQLite version 3.29.0 2019-07-10 17:32:03
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 int primary key, Info text);
QUERY PLAN
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)
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 * from TheTable;
QUERY PLAN
`--SCAN TABLE TheTable
ID|Info
1|Test
2|Test2
|TestNull1
|TestNull2
|TestNull3
sqlite> select count(distinct id) from theTable;
QUERY PLAN
`--SCAN TABLE theTable USING COVERING INDEX sqlite_autoindex_TheTable_1
count(distinct id)
2
sqlite> select id, count(*) from theTable group by id order by id;
QUERY PLAN
`--SCAN TABLE theTable USING COVERING INDEX sqlite_autoindex_TheTable_1
ID|count(*)
|3
1|1
2|1
sqlite>
-----Original Message-----
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of
Stephen Chrzanowski
Sent: Thursday, July 18, 2019 1:18 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] I can insert multiple rows with the same primary key when
one of the value of the PK is NULL ...
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
