> Le 18 juil. 2019 à 18:05, J. King <jk...@jkingweb.ca> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
