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