On 15 Dec 2012, at 6:26pm, Peter Haworth <p...@lcsql.com> wrote: > If I understand the docs correctly, the REPLACE part of an INSERT OR > REPLACE command deletes a row if a UNIQUE constraint occurs and inserts a > new row in its place.
Correct. f that commend decides a REPLACE is needed the original row will indeed be deleted. > On the face of it, that seems like it will > invalidate any foreign key definitions that use the primary key of the > INSERT OR REPLACE table as a parent link, and it doesn't feel like ON > DELETE or ON UPDATE actions can handle that situation. Well, as you suspect they handle it by doing their jobs. Both the DELETE and INSERT actions are triggered. > Is there a way to deal with this situation or should I not use INSERT OR > REPLACE on a table that is the parent of a foreign key definition? I think you 'should not use'. You can do an UPDATE and see whether it updated anything using the sqlite3_changes() API call or the changes() SQL function. Then you can decide whether you want to do the INSERT. Or you can do a SELECT to find out if there're any existing rows before you do your INSERT. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
