> INSERT OR REPLACE, where the new record is a duplicate, does a DELETE and > then an INSERT. In other words, there’s a short time when the old record > has been deleted but the new record hasn’t been inserted yet. The result > is not the same as if SQLite did an UPDATE instead.
Since the statement is executed inside a transaction thusly: BEGIN; DELETE ... INSERT ... COMMIT; an external viewer can see no difference between an UPDATE or a REPLACE with the single exception that if you are enforcing foreign keys and there is a foreign key dependancy (with cascade) on the row being deleted, all the children will be deleted also. sqlite> create table x(id integer primary key, desc text collate nocase); sqlite> create table y(fk integer references x on delete cascade, descy text collate nocase); sqlite> insert into x values (1, 'new 1'); sqlite> insert into y values (1, 'depends on new 1'); sqlite> replace into x values (1, 'replace 1'); sqlite> select * from x; 1|replace 1 sqlite> select * from y; sqlite> If no on delete constraint were specified, you get this: sqlite> create table x(id integer primary key, desc text collate nocase); sqlite> insert into x values (1, 'new 1'); sqlite> create table y(fk integer references x, descy text collate nocase); sqlite> insert into y values (1, 'depends on new 1'); sqlite> replace into x values (1, 'replace 1'); sqlite> select * from y; 1|depends on new 1 sqlite> select * from x; 1|replace 1 However, if you had ON DELETE RESTRICT, you would get this: sqlite> create table x(id integer primary key, desc text collate nocase); sqlite> insert into x values (1, 'new 1'); sqlite> create table y(fk integer references x on delete restrict, descy text collate nocase); sqlite> insert into y values (1, 'depends on new 1'); sqlite> replace into x values (1, 'replace 1'); Error: FOREIGN KEY constraint failed Of course, the above only applies if the "duplicate" is the ROWID. If it is some other constraint your results will vary. However, the fact is that on EVERY OTHER connection, the REPLACE is atomic and from the perspective of another connection there is NO INSTANT IN TIME (no matter how small) at which the row does not exist. > An additional complication is that for some purposes SQLite considers that > two different NULLs are not the same value. But not all purposes. So you > have to know exactly what you’re doing if you’re messing with NULL values > in primary keys. This does not apply to ROWID's (which cannot be NULL). _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
