Is there any way to perform the equivalent of an INSERT OR UPDATE statement?
I have something like this: CREATE TABLE t1 (id INTEGER PRIMARY KEY, value INTEGER); CREATE TABLE t2 (parent INTEGER REFERENCES t1 ON DELETE CASCADE, child INTEGER REFERENCES t1); INSERT INTO t1 VALUES(1,100); INSERT INTO t1 VALUES(2,200); INSERT INTO t2 VALUES(1,2); Later on, I want to change the value of t1(1,100) to (1,101) or insert a new row, I don't know which. I realize I can do INSERT OR REPLACE, but if I have a foreign key referencing that table with CASCADE DELETE, the delete propagates and t2(1,2) is removed. Of course I can first test for the existence of the row with a separate SQL statement, but I want to make sure I'm not missing something, being an SQL neophyte. I thought that perhaps I could make my foreign key reference DEFERRABLE INITIALLY DEFFERED, but that doesn't have any effect on CASCADE DELETE. I looked at the documentation for ON CONFLICT clause, but didn't see anything relevant there [N.B. in the documentation for ON CONFLICT ABORT, it says "This is the default behavior and the behavior proscribed the SQL standard". I'm a rookie, but I'm pretty sure that must mean "the behavior prescribed by the SQL standard" - one letter difference but caused me some angst.] Any help is greatly appreciated. Regards, Keith _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
