On Apr 8, 2010, at 4:33 AM, P Kishor wrote: > On Wed, Apr 7, 2010 at 4:24 PM, Pavel Ivanov <[email protected]> > wrote: >>> I get a "Error: constraint failed". I have no constraint other than >>> INTEGER PRIMARY KEY on id. >> >> You should have something other than integer primary key, otherwise >> it works: >> >> sqlite> create table t (id integer primary key, foo, bar); >> sqlite> insert into t values (649, 'foo 1', 'bar 1'); >> sqlite> insert into t values (651, 'foo 2', 'bar 2'); >> sqlite> .h on >> sqlite> select * from t; >> id|foo|bar >> 649|foo 1|bar 1 >> 651|foo 2|bar 2 >> sqlite> replace into t (id, foo, bar) select 649, foo, bar from t >> where id = 651; >> sqlite> select * from t; >> id|foo|bar >> 649|foo 2|bar 2 >> 651|foo 2|bar 2 >> >> > > > The only other stuff going on in my db is an FTS3 virtual table with > triggers that fire on update/insert/delete. Logically, that shouldn't > matter, because the REPLACE should be treated as a normal UPDATE, so > the FTS tables should get updated by the triggers without any problem. > Maybe REPLACE gets treated as an INSERT,
I think this is correct. The 'on conflict' clause of a DML statement (in this case REPLACE) is not passed to virtual tables. To virtual tables, REPLACE==INSERT. Related fact: Normally, REPLACE only fires INSERT triggers. But if you set "PRAGMA recursive_triggers = ON", then it fires DELETE triggers for the rows it removes too. Dan. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
