On Wed, Jan 12, 2011 at 4:54 PM, Duquette, William H (318K) < william.h.duque...@jpl.nasa.gov> 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 -- william.h.duque...@jpl.nasa.gov > Athena Development Lead -- Jet Propulsion Laboratory > "It's amazing what you can do with the right tools." > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users