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. 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