Hi,
I have read that INSERT OR REPLACE is equivalent to a DELETE followed by an
INSERT. I came across a case where that is not true.
Set up a test case
$ rm -f asdf.sqlite && sqlite3 asdf.sqlite
sqlite> .mode columns
sqlite> .headers on
sqlite> PRAGMA foreign_keys=OFF;
sqlite> CREATE TABLE users (
...> id INT UNSIGNED NOT NULL PRIMARY KEY,
...> username TEXT NOT NULL
...> );
sqlite> CREATE TABLE user_extras (
...> user_id INT UNSIGNED NOT NULL PRIMARY KEY,
...> other INT NULL DEFAULT NULL,
...> FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE
CASCADE ON DELETE CASCADE
...> );
sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
sqlite> INSERT INTO user_extras (user_id, other) VALUES (1, 33);
sqlite> SELECT * FROM users;
id username
---------- ----------
1 asdf
sqlite> SELECT * FROM user_extras;
user_id other
---------- ----------
1 33
Let's try a delete followed by an insert.
sqlite> DELETE FROM users WHERE id = 1;
sqlite> SELECT * FROM users;
sqlite> SELECT * FROM user_extras;
user_id other
---------- ----------
1 33
sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
sqlite> SELECT * FROM users;
id username
---------- ----------
1 asdf
sqlite> SELECT * FROM user_extras;
user_id other
---------- ----------
1 33
Notice that the value `user_extras.other` is still 33. That's good.
sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1, 'asdf');
sqlite> SELECT * FROM users;
id username
---------- ----------
1 asdf
sqlite> SELECT * FROM user_extras;
user_id other
---------- ----------
1 33
Still 33. That's good.
Based on the above, it looks like `INSERT OR REPLACE` is equivalent to a
`DELETE` followed by an `INSERT`.
If we add a trigger, things change...
sqlite> CREATE TRIGGER users_after_insert AFTER INSERT ON users
...> BEGIN
...> INSERT OR IGNORE INTO user_extras (user_id) VALUES (new.id);
...> END;
A `DELETE` followed by an `INSERT` still works as before.
sqlite> DELETE FROM users WHERE id = 1;
sqlite> SELECT * FROM users;
sqlite> SELECT * FROM user_extras;
user_id other
---------- ----------
1 33
sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
sqlite> SELECT * FROM users;
id username
---------- ----------
1 asdf
sqlite> SELECT * FROM user_extras;
user_id other
---------- ----------
1 33
See above that the value of `user_extras.other` survived the `DELETE`
followed by the `INSERT`.
When we use `INSERT OR REPLACE` the `user_extras.other` value is cleared
out.
sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1, 'asdf');
sqlite> SELECT * FROM users;
id username
---------- ----------
1 asdf
sqlite> SELECT * FROM user_extras;
user_id other
---------- ----------
1
Because of the trigger, `INSERT OR REPLACE` is no longer equivalent to
`DELETE` followed by `INSERT`.
Peter
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
