On 2018/03/09 9:49 PM, John Found wrote:
On Fri, 9 Mar 2018 19:42:19 +0000
Simon Slavin <slav...@bigfraud.org> wrote:
You are right. And Jay Kreibich in his post above. But then the second
solution from my post should be the correct behavior.
In the current implementation "insert or replace" behave as the foreign
constraint is deferred.
But according to documentation, all foreign constraints in SQLite are immediate
by default.
If an FK is created as immediate, it will fail at the conclusion (read:
END OF) the statement that hits the FK check. If it is declared as
DEFERRED it will wait all the way until the end of the Transaction when
you try to commit.
Either way, many people get confused with REPLACE because it feels like
UPDATE, it is not, REPLACE means (as have been pointed out) "REMOVE
constraint-violating rows, THEN, ADD the given new row" - and more
importantly - all of this happens INSIDE one single statement so that
any FK checks will happen at the end of said statement AFTER the new row
is added back, and so no constraint violation exists come checking time.
Triggers are a whole different matter, they are like EVENT handlers, and
have to fire by binding contract the very moment the triggerable offense
happens, which in the case of REPLACE is right in the middle of the
statement, if (and only if) there was one or more rows in there that
needed ousting.
What you probably wanted is an "UPSERT", which in theory is more an
UPDATE OR INSERT than a REPLACE, and will cause at best an UPDATE
Trigger to fire and no deletions will happen. SQLite doesn't have a
command like that, but you can easily simulate it by just issuing two
commands:
First do the UPDATE... WHERE Key = X - which, if the record doesn't
exist yet, will fail quietly because of the WHERE clause,
then do the INSERT OR IGNORE(...) - which will again fail quietly if it
did already exist,
both of which won't break a Transaction, mess with the wrong triggers or
constraint checks, will be very fast (considering the PK is used and
only one of the two statements gets to do any work in IO terms)... and
will always work perfectly and reliably fire only ON INSERT, ON UPDATE
and ON DELETE triggers when those really happen.
The obvious downside to it being you having to formulate both an UPDATE
and an INSERT statement in your code, which can be irritating, but then,
if it's the best tool for the job...
I hope this shed some light on how it works and why the triggers and FKs
fire differently (by design) and how to get the results you want.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
