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
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
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
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
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
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
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
> 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
"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
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.
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
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.
> 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")
>>
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
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
15 matches
Mail list logo
