SQLite not cascading updates or deletes
lazarus / 16-Databases

----------------------------------------------------------------------
Manuel Santana · 2015-06-08 15:38

Hi, you all! Greetings from Dominican Republic. I'm new here, so bear
with me. I'm having a problem with SQLite: I have two tables with
referential integrity set up with cascading of the updates, everything
works fine with any of the SQlite admin tools, but when I update the
parent table from db.executedirect(), the child records don't get
updated. I pass the "PRAGMA foreign_keys=ON;" to the connection params
before any transaction, and no luck.

Any ideas?

 memLog.Clear;
  memLog.Lines.Add('=== Start: ' + DateTimeToStr(Now));

  memLog.Lines.Add('Opening connection...');
  conn.Open;
  if conn.Connected then
    memLog.Lines.Add('Opening connection...done');


  memLog.Lines.Add('Opening transaction');
  tran.Active:=True;
  if tran.Active then
    memLog.Lines.Add('Opening transaction...done');

  cSQL := 'update padres set id = id * 100;';
  //cSQL := 'insert into hijos(nombre,id_padre)
values(''TEST'',999);';

  memLog.Lines.Add('Updating table...');
  conn.ExecuteDirect(cSQL);
  tran.Commit;
  memLog.Lines.Add('Updating table...done');

  memLog.Lines.Add('Closing connection...');
  conn.Close;
  memLog.Lines.Add('Closing connection...done');
  memLog.Lines.Add('=== Finished: ' + DateTimeToStr(Now));     

Windows 7 ult.
Sqlite 3.8.10.2
Lazarus 1.4.0
sqldb components (TSQLite3Connection).

----------------------------------------------------------------------
rvk · 2015-06-08 18:00

I don't think you need to (or must NOT) put PRAGMA in front of it.
foreign_keys=on should be enough.

After setting 'foreign_keys=on' in Params you can check if it's really
set:

Code: [Select]SQLite3Connection1.Params.Add('foreign_keys=on');
SQLite3Connection1.Connected := True;

// this will check if foreign_key support is on
// you can delete this check if all goes well
with TSQLQuery.Create(nil) do
begin
  Database := SQLite3Connection1;
  SQL.Text := 'PRAGMA foreign_keys;';
  Open;
  ShowMessage('PRAGMA foreign_keys = ' + fields[0].AsString);
  Free;
end;

PRAGMA is for executing the command directly but TSQLite3Connection
does that for you if foreign_keys=on.

----------------------------------------------------------------------
Manuel Santana · 2015-06-09 00:27

Thanks...it works now. The problem was that I was setting it
"foreign_keys = on" with spaces between the = sign and apparently, it
must be set without spaces: foreign_keys=on

BTW, the pragma keyword must not be used as you said.

Thank you very much.