Recent

Author Topic: [SOLVED] Relational database  (Read 574 times)

Pe3s

  • Sr. Member
  • ****
  • Posts: 326
[SOLVED] Relational database
« on: November 18, 2022, 05:33:21 pm »
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
« Last Edit: November 19, 2022, 10:02:43 am by Pe3s »

Thaddy

  • Hero Member
  • *****
  • Posts: 12585
Re: Relational database
« Reply #1 on: November 18, 2022, 06:53:47 pm »
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
« Last Edit: November 18, 2022, 06:57:08 pm by Thaddy »
Writing code in an overly complex way is usually merely a complete misunderstanding of the goal you are trying to achieve.

paweld

  • Hero Member
  • *****
  • Posts: 546
Re: Relational database
« Reply #2 on: November 18, 2022, 06:56:48 pm »
Best regards / Pozdrawiam
paweld

Thaddy

  • Hero Member
  • *****
  • Posts: 12585
Re: Relational database
« Reply #3 on: November 18, 2022, 06:57:42 pm »
posts crossed
Writing code in an overly complex way is usually merely a complete misunderstanding of the goal you are trying to achieve.

Pe3s

  • Sr. Member
  • ****
  • Posts: 326
Re: Relational database
« Reply #4 on: November 18, 2022, 08:17:18 pm »
Thank you  :)

Pe3s

  • Sr. Member
  • ****
  • Posts: 326
Relational database
« Reply #5 on: November 18, 2022, 08:55:42 pm »
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

  • Hero Member
  • *****
  • Posts: 546
Re: Relational database
« Reply #6 on: November 18, 2022, 09:16:25 pm »
You have open dataset - close it. After 5th line add:
Code: Pascal  [Select][+][-]
  1. ZQuery1.Close;
Best regards / Pozdrawiam
paweld

Pe3s

  • Sr. Member
  • ****
  • Posts: 326
Re: Relational database
« Reply #7 on: November 18, 2022, 09:35:46 pm »
SQL error logic ?

dseligo

  • Hero Member
  • *****
  • Posts: 876
Re: Relational database
« Reply #8 on: November 18, 2022, 09:38:29 pm »
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

  • Sr. Member
  • ****
  • Posts: 326
Re: Relational database
« Reply #9 on: November 19, 2022, 10:02:27 am »
Thank you  :)

Zvoni

  • Hero Member
  • *****
  • Posts: 1591
Re: [SOLVED] Relational database
« Reply #10 on: November 19, 2022, 07:09:14 pm »
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.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018