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? "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 ignored. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
