[SOLVED] Relational database

[Pe3s]

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]

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]

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

[Thaddy]

posts crossed

[Pe3s]

Thank you 

[Pe3s]

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button10Click(Sender: TObject);
2. begin
3. if MessageDlg('Question', 'Delete record', mtWarning, mbYesNo, 0) = mrYes then
4. begin
5.  iId := ZQuery1.FieldByName('Category_ID').AsInteger;
6.  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';
7.  ZQuery1.ParamByName('Category_ID').AsInteger := iId;
8.  ZQuery1.ExecSQL;
9.  
10.  ZQuery1.SQL.Text := 'SELECT * FROM database';
11.  ZQuery1.Open;
12. end;
13. end;
14.  

Why is the code not working , where is the error ?

[paweld]

You have open dataset - close it. After 5th line add:

Code: Pascal  [Select][+][-]

1. ZQuery1.Close;

[Pe3s]

SQL error logic ?

[dseligo]

Why is the code not working , where is the error ?

If 'database' and 'database1' are your table names then try this:

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button10Click(Sender: TObject);
2. begin
3. if MessageDlg('Question', 'Delete record', mtWarning, mbYesNo, 0) = mrYes then
4. begin
5.  iId := ZQuery1.FieldByName('Category_ID').AsInteger;
6.  ZQuery1.SQL.Text := 'DELETE FROM database WHERE Category_ID = :Category_ID';
7.  ZQuery1.ParamByName('Category_ID').AsInteger := iId;
8.  ZQuery1.ExecSQL;
9.  ZQuery1.SQL.Text := 'DELETE FROM database1 WHERE Category_ID = :Category_ID';
10.  ZQuery1.ParamByName('Category_ID').AsInteger := iId;
11.  ZQuery1.ExecSQL;
12.  
13.  ZQuery1.SQL.Text := 'SELECT * FROM database';
14.  ZQuery1.Open;
15. end;
16. end;
17.  

[Pe3s]

Thank you 

[Zvoni]

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.