Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
Hi Alex,
On Thu, 18 Jul 2019 at 17:02, Alexandre Billon 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.
You quote "https://sqlite.org/lang_createtable.html#constraints;,
which contains:
<<
Each row in a table with a primary key must have a unique combination
of values in its primary key columns. For the purposes of determining
the uniqueness of primary key values, NULL values are considered
distinct from all other values, including other NULLs.
>>
I.e the rows you are inserting do not have conflicting primary keys
The paragraph following the one quoted above is also relevant...
> Thanks in advance for your insights.
>
> Alex
Regards,
Simon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
Well, yes and no. I see that as more of a generic question of "why is some rogue process accessing and changing your database?" rather than a problem specific to SQLite. If your data needs foreign keys, or some extension like FTS, R-Tree, etc. then you're going to be controlling what's accessing and changing your database and make sure it knows what's in there. If some other process is bludgeoning its way through your data without respect, then that's another whole issue that would be there no matter how you chose to store your data. -Original Message- From: sqlite-users On Behalf Of Richard Damon Sent: Friday, July 19, 2019 2:46 PM 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 ... One big issue is that in general (as I remember right) pragmas generally affect the connection, not the database itself, so shouldn’t change how the schema is interpreted, or another connection (or before issuing the pragma) might interpret things differently and possibly see the database as somehow corrupt or be able to corrupt the database. > On Jul 19, 2019, at 1:44 PM, Thomas Kurz wrote: > > Imho it would be helpful (especially for newbies that don't know the full > history of SQLite) to have a "PRAGMA strict_mode" or similar, to disable all > kinds of historical bugs. They might be relevant for existing applications > but in no way for newly created ones. Among the things to consider should be: > > - PRIMARY KEY = UNIQUE NOT NULL (what I have learned now *g*) > - enable FOREIGN KEY constraints (I know there is already a pragma, but imho > it should be included) > - strict type enforcement > - disable the use of double quotes for strings > - default to WITHOUT ROWID > > ...and probably many more I don't know about ;-) > > ___ 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
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
One big issue is that in general (as I remember right) pragmas generally affect the connection, not the database itself, so shouldn’t change how the schema is interpreted, or another connection (or before issuing the pragma) might interpret things differently and possibly see the database as somehow corrupt or be able to corrupt the database. > On Jul 19, 2019, at 1:44 PM, Thomas Kurz wrote: > > Imho it would be helpful (especially for newbies that don't know the full > history of SQLite) to have a "PRAGMA strict_mode" or similar, to disable all > kinds of historical bugs. They might be relevant for existing applications > but in no way for newly created ones. Among the things to consider should be: > > - PRIMARY KEY = UNIQUE NOT NULL (what I have learned now *g*) > - enable FOREIGN KEY constraints (I know there is already a pragma, but imho > it should be included) > - strict type enforcement > - disable the use of double quotes for strings > - default to WITHOUT ROWID > > ...and probably many more I don't know about ;-) > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
Imho it would be helpful (especially for newbies that don't know the full history of SQLite) to have a "PRAGMA strict_mode" or similar, to disable all kinds of historical bugs. They might be relevant for existing applications but in no way for newly created ones. Among the things to consider should be: - PRIMARY KEY = UNIQUE NOT NULL (what I have learned now *g*) - enable FOREIGN KEY constraints (I know there is already a pragma, but imho it should be included) - strict type enforcement - disable the use of double quotes for strings - default to WITHOUT ROWID ...and probably many more I don't know about ;-) - Original Message - From: Dominique Devienne To: SQLite mailing list Sent: Friday, July 19, 2019, 10:25:17 Subject: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ... On Thu, Jul 18, 2019 at 9:11 PM Keith Medcalf wrote: > 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. This whole thread is good material for the new(ish) quirks page IMHO. As Keith points out, most of the material exists in the doc already, but IMHO the quirks page should be the one-stop page to learn about all those historical or by-design particularities of SQLite, with a short explanation and pointers to other doc places with more details. Just my $0.02 of course :) --DD ___ 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
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
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
On Thu, Jul 18, 2019 at 9:11 PM Keith Medcalf wrote: > 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. > This whole thread is good material for the new(ish) quirks page IMHO. As Keith points out, most of the material exists in the doc already, but IMHO the quirks page should be the one-stop page to learn about all those historical or by-design particularities of SQLite, with a short explanation and pointers to other doc places with more details. Just my $0.02 of course :) --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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
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
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
"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 On Behalf Of
Stephen Chrzanowski
Sent: Thursday, July 18, 2019 1:18 PM
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 ...
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
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
> 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:
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
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
> 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 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
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 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 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
On 18 Jul 2019, at 5:02pm, Alexandre Billon wrote:
> INSERT INTO sales ("client", "salesman", "revenue")
> VALUES ('C1', NULL, 10.0);
The value NULL in SQLite means 'value unknown'. It is a special case. If you
compare two NULLs SQLite will act as if those values are different. Therefore
you can have two PRIMARY KEYs with NULL in the same place.
> 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
As you see here when you try the sme thing with non-NULL values, you get the
error you expected.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
> Le 18 juil. 2019 à 18:05, J. King a écrit :
>
>> 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 ?
>>
> It is indeed normal behaviour. See for
> an explanation. If you want a true primary key, use a without rowid table:
>
> CREATE TABLE "sales" (
> "client"TEXT,
> "salesman" TEXT,
> "revenue" REAL,
> PRIMARY KEY("client","salesman")
> ) WITHOUT ROWID;
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).
https://www.sqlite.org/quirks.html#primary_keys_can_sometimes_contain_nulls
Then, now that you know why NULL is accepted (when you rightly would have
expected it to be refused), you can insert many times because each NULL is
treated distinct in a UNIQUE column.
https://www.sqlite.org/nulls.html
—
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten
Grüßen,
Olivier Mascia
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
On 2019-07-18 12:02:18, "Alexandre Billon" 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 ?
It is indeed normal behaviour. See
for an explanation. If you want a true primary key, use a without rowid
table:
CREATE TABLE "sales" (
"client" TEXT,
"salesman"TEXT,
"revenue" REAL,
PRIMARY KEY("client","salesman")
) WITHOUT ROWID;
--
J. King
___
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
