I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

Alexandre Billon
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

J. King-3

On 2019-07-18 12:02:18, "Alexandre Billon" <[hidden email]> 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 <https://sqlite.org/rowidtable.html>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

Olivier Mascia
> Le 18 juil. 2019 à 18:05, J. King <[hidden email]> 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 <https://sqlite.org/rowidtable.html> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

Simon Slavin-3
In reply to this post by Alexandre Billon
On 18 Jul 2019, at 5:02pm, Alexandre Billon <[hidden email]> 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.

<https://www.sqlite.org/nulls.html>

> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

Stephen Chrzanowski
In reply to this post by Alexandre Billon
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

Stephen Chrzanowski
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 <[hidden email]>
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 <[hidden email]>
> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

David Raymond
"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 <[hidden email]> On Behalf Of Stephen Chrzanowski
Sent: Thursday, July 18, 2019 1:18 PM
To: SQLite mailing list <[hidden email]>
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 <[hidden email]>
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 <[hidden email]>
> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

Thomas Kurz
In reply to this post by Olivier Mascia
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

Keith Medcalf

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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

Dominique Devienne
On Thu, Jul 18, 2019 at 9:11 PM Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

Alexandre Billon
In reply to this post by Keith Medcalf
Thank you all for your answers.

I have made the changes necessary.

Alex

-----Message d'origine-----
De : sqlite-users [mailto:[hidden email]] De la part de Keith Medcalf
Envoyé : jeudi 18 juillet 2019 21:11
À : SQLite mailing list <[hidden email]>
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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

Thomas Kurz
In reply to this post by Dominique Devienne
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 <[hidden email]>
To: SQLite mailing list <[hidden email]>
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

Richard Damon
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

David Raymond
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 <[hidden email]> On Behalf Of Richard Damon
Sent: Friday, July 19, 2019 2:46 PM
To: SQLite mailing list <[hidden email]>
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

Simon Davies
In reply to this post by Alexandre Billon
Hi Alex,

On Thu, 18 Jul 2019 at 17:02, Alexandre Billon <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users