On Wed, Jan 12, 2011 at 4:54 PM, Duquette, William H (318K) <
[email protected]> wrote:
> I've just discovered that a REPLACE can trigger a
> cascading delete. Is this expected behavior?
>
> I have an undo scheme where I grab entire rows from the
> database before they are changed; then, on undo I
> simply put the rows back using "INSERT OR REPLACE".
> My assumption was that doing a REPLACE was
> equivalent to doing an UPDATE on the non-key
> values given the key values. Apparently not.
>
Nope. REPLACE is a shorthand for DELETE followed by INSERT. Note that if
there are multiple uniqueness constraints on a table, a REPLACE might get
translated into multiple DELETEs (one for each uniqueness constraint)
followed by a single INSERT. Hence a REPLACE can reduce the number of rows
in a table.
>
> Here's some SQL that illustrates the problem:
>
> PRAGMA foreign_keys=1;
>
> CREATE TABLE parent(
> parent_id TEXT PRIMARY KEY,
> value TEXT);
>
> CREATE TABLE child(
> child_id INTEGER PRIMARY KEY,
> parent_id TEXT REFERENCES parent(parent_id)
> ON DELETE CASCADE
> DEFERRABLE INITIALLY DEFERRED,
> value TEXT);
>
> INSERT INTO parent(parent_id, value) VALUES('FRED', 1);
> INSERT INTO parent(parent_id, value) VALUES('GEORGE', 2);
>
> INSERT INTO child(parent_id, value) VALUES('FRED', 'FOO');
> INSERT INTO child(parent_id, value) VALUES('FRED', 'BAR');
> INSERT INTO child(parent_id, value) VALUES('GEORGE', 'FOO');
>
> -- Causes the records in child that reference FRED
> -- to be deleted.
> REPLACE INTO parent(parent_id, value) VALUES('FRED',3);
>
>
>
> Will Duquette -- [email protected]
> Athena Development Lead -- Jet Propulsion Laboratory
> "It's amazing what you can do with the right tools."
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
