[SOLVED] Relational database
lazarus / 16-Databases

----------------------------------------------------------------------
Pe3s · 2022-11-18 16:33

Hello I have this question, I have a SQLite relational database with
two tables. A one-to-many relationship. If I delete a record from
Table 1 it does not delete records in Table 2. How can I delete the
record from table 1 and all records related to the record in table 2 ?
Greetings

----------------------------------------------------------------------
Thaddy · 2022-11-18 17:53

DELETE <X> FROM <Y> JOIN X1 WHERE <value> or similar. (or a double
select)
But in a.o Sqlite you can use:
https://www.techonthenet.com/sqlite/foreign_keys/foreign_delete.php

----------------------------------------------------------------------
paweld · 2022-11-18 17:56

Foregin Keys: https://www.sqlite.org/foreignkeys.html

----------------------------------------------------------------------
Thaddy · 2022-11-18 17:57

posts crossed

----------------------------------------------------------------------
Pe3s · 2022-11-18 19:17

Thank you

----------------------------------------------------------------------
Pe3s · 2022-11-18 19:55

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button10Click(Sender: TObject);beginif MessageDlg('Question',
'Delete record', mtWarning, mbYesNo, 0) = mrYes thenbegin iId :=
ZQuery1.FieldByName('Category_ID').AsInteger; ZQuery1.SQL.Text :=
'DELETE x FROM database AS x JOIN database1 AS y ON x.Category_ID =
y.Category_ID WHERE Category_ID =:
Category_ID'; ZQuery1.ParamByName('Category_ID').AsInteger :=
iId; ZQuery1.ExecSQL;  ZQuery1.SQL.Text := 'SELECT * FROM
database'; ZQuery1.Open;end;end; 
Why is the code not working , where is the error ?

----------------------------------------------------------------------
paweld · 2022-11-18 20:16

You have open dataset - close it. After 5th line add:Code: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
ZQuery1.Close;

----------------------------------------------------------------------
Pe3s · 2022-11-18 20:35

SQL error logic ?

----------------------------------------------------------------------
dseligo · 2022-11-18 20:38

Quote from: Pe3s on November 18, 2022, 08:55:42 pmWhy is the code not
working , where is the error ?

If 'database' and 'database1' are your table names then try this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button10Click(Sender: TObject);beginif MessageDlg('Question',
'Delete record', mtWarning, mbYesNo, 0) = mrYes thenbegin iId :=
ZQuery1.FieldByName('Category_ID').AsInteger; ZQuery1.SQL.Text :=
'DELETE FROM database WHERE Category_ID =
:Category_ID'; ZQuery1.ParamByName('Category_ID').AsInteger :=
iId; ZQuery1.ExecSQL; ZQuery1.SQL.Text := 'DELETE FROM database1 WHERE
Category_ID =
:Category_ID'; ZQuery1.ParamByName('Category_ID').AsInteger :=
iId; ZQuery1.ExecSQL;  ZQuery1.SQL.Text := 'SELECT * FROM
database'; ZQuery1.Open;end;end;

----------------------------------------------------------------------
Pe3s · 2022-11-19 09:02

Thank you

----------------------------------------------------------------------
Zvoni · 2022-11-19 18:09

IIRC, SQLiteconnection turns on foreign_keys PRAGMA to on (think i saw
it somewhere deep down in its sourcecode).

That said: if you don‘t create your detail-table with foreign key with
option ON DELETE CASCADE, then you get what‘s happening to you.