based on my reading of the docs for INSERT OR REPLACE, it will delete rows for ANY constraint violation, not just one involving the primary key. Is that reading wrong?
-James > On Thu, 02 Jul 2009 19:28:17 -0700, James Gregurich > <bayoubenga...@mac.com> wrote: > > > > >question: > > > >How do I maintain referential integrity on a INSERT OR REPLACE given > >it does not call the delete trigger on the offending rows? > > Please correct me if I'm wrong, but considering the two > cases INSERT OR REPLACE handles for the referenced table: > > 1.there was no row with that primary key (PK) > the INSERT part of the statement is used, > any AFTER INSERT trigger is executed > > 2.there already was a row with that PK > the REPLACE part of the statement is used, so > DELETE, then INSERT. > After that, there still is a row with that PK. > There is no reason to trigger cascading deletes > in referring tables, or to forbid the deletion ... > > And considering INSERT OR REPLACE of rows in the referring > table (the one with the foreign key), only the INSERT > trigger has to fire to ensure the FK refers to an existing > PF in the referred table ... > > ... I would say the DELETE TRIGGER doesn't have to fire on > INSERT OR REPLACE. > -- > ( Kees Nuyt > ) > c[_] > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
