On Saturday, 17 September, 2016 21:35, mikeeggl asked:

> In my DDL I have “integer primary key not null” on my tables. When I do
> the first insert to these tables the rowid is assigned. The insert
> statement is a “insert or replace into…” so I don’t have to deal with
> insertion errors. However, I expected the rowid to not change when the row
> has not changed (when the replace is part of the clause is used). Is there
> a way to not have the rowid changed when “insert or replace into…” is
> used?

Specify the integer primary key in the insert or replace?


INSERTs a row if it does not cause an integrity violation.
if the attempt to INSERT the row causes an integrity violation, then REPLACE 
deletes (all) the conflicting row(s) then does the INSERT.

If no integer primary key value is specified (ie, is null) when the INSERT 
(either the initial INSERT, or the one after the conflicting rows have been 
removed), then a new integer primary key is generated.

If you do not want the OR REPLACE then perhaps OR IGNORE is what you want.  
Possibly preceded by one or more conditional UPDATE(s) to update any 
pre-existing rows.

INTEGER PRIMARY KEY is always not null since the ROWID cannot be null.  Your 
specification of not null is redundant.  The specification of NULL or NOT NULL 
on integer primary key columns (even for WITHOUT ROWID tables) is silently 

sqlite-users mailing list

Reply via email to