How to apply a cached update FDQuery using Delphi FireDAC with an UNIQUE constraint on the database

Update Since writing the original version of this answer, I've done some more investigation and am beginning to think that either there is a problem with ApplyUpdates, etc, in FireDAC's support for Sqlite (in Seattle, at least), or we are not using the FD components correctly. It would need FireDAC's author (who is a contributor here) to say which it is.

Leaving aside the ApplyUpdates business for a moment, there are a number of other problems with your code, namely your dataset navigation makes assumptions about the ordering on the rows in qry and the numbering of its Fields.

The test case I have used is to start (before execution of the application) with the Foo table containing the single row

(1, 'R1')

Then, I execute the following Delphi code, at the same time as monitoring the contents of Foo using an external application (the Sqlite Manager plug-in for FireFox). The code executes without an error being reported in the application, but notice that it does not call ApplyUpdates.

  Con.Open();  Con.StartTransaction;  qry.Open('select * from FOO');  qry.InsertRecord([2, 'TT']);  assert(qry.Locate('ID', 1, []));  qry.Edit;  qry.FieldByName('DESC').AsString := 'R2';  qry.Post;  assert(qry.Locate('ID', 2, []));  qry.Edit;  qry.FieldByName('DESC').AsString := 'R1';  qry.Post;  Con.Commit;  qry.Close;  Con.Close;

The added row (ID = 2) is not visible to the external application until after Con.Close has executed, which I find puzzling. Once Con.Close has been called, the external application shows Foo as containing

(1, 'R2')(2, 'R1')

However, I have been unable to avoid the constraint violation error if I call ApplyUpdates, regardless of any other changes I make to the code, including adding a call to ApplyUpdates after the first Post.

So, it seems to me that either the operation of ApplyUpdates is flawed or it is not being used correctly.

I mentioned FireDAC's author. His name is Dmitry Arefiev and he has answered a lot of FD qs on SO, though I haven't noticed him here in the past couple of months or so. You might try catching his attention by posting in EMBA's FireDAC NG forum, https://forums.embarcadero.com/forum.jspa?forumID=502.