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

[J. King]

On 2019-07-18 12:02:18, "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 ?

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