Recent

Author Topic: Using multiple foreign keys (SQLite)  (Read 16383 times)

nikel

  • Full Member
  • ***
  • Posts: 186
Using multiple foreign keys (SQLite)
« on: April 27, 2018, 06:27:28 am »
Hello, I'm here again! I'm trying to create some master/detail fields using SQLite. I have 4 DBGrids on my form (I'll change 3 of them to comboboxes) and I want to populate them with db tables. Here's the structure of my db:

Code: SQL  [Select][+][-]
  1. CREATE TABLE "tbl_authors"
  2. (
  3.         "int_author_id" INTEGER PRIMARY KEY  NOT NULL ,
  4.         "txt_author" VARCHAR(100) NOT NULL ,
  5.         "int_nationality_id" INTEGER NOT NULL  DEFAULT (3),
  6.         FOREIGN KEY (int_nationality_id) REFERENCES tbl_nationalities(int_nationality_id)
  7. );
  8.  
  9. CREATE TABLE "tbl_categories"
  10. (
  11.         "int_category_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
  12.         "txt_category" VARCHAR(75) NOT NULL  UNIQUE
  13. );
  14.  
  15. CREATE TABLE "tbl_nationalities"
  16. (
  17.         "int_nationality_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
  18.         "txt_nationality" VARCHAR(50) NOT NULL  UNIQUE
  19. );
  20.  
  21. CREATE TABLE "tbl_quotes"
  22. (
  23.         "int_quote_id" INTEGER PRIMARY KEY  NOT NULL ,
  24.         "txt_quote" VARCHAR(500) NOT NULL  DEFAULT (NULL) ,
  25.         "int_author_id" INTEGER NOT NULL,
  26.         "int_category_id" INTEGER NOT NULL  DEFAULT 1,
  27.         FOREIGN KEY (int_author_id) REFERENCES tbl_authors(int_author_id),
  28.         FOREIGN KEY (int_category_id) REFERENCES tbl_categories(int_category_id)
  29. );

I have 1 SQLite3Connection, 1 SQLTransaction, 4 DataSources and 4 SQLQueries on myform. I uploaded a screenshot regarding how I linked them to each other: https://i.hizliresim.com/1J5WLN.png

And here are my queries:
qryNationalities
Code: SQL  [Select][+][-]
  1. SELECT int_nationality_id, txt_nationality FROM tbl_nationalities;

qryAuthors
Code: SQL  [Select][+][-]
  1. SELECT int_author_id, txt_author, int_nationality_id FROM tbl_authors;

qryCategories
Code: SQL  [Select][+][-]
  1. SELECT int_category_id, txt_category FROM tbl_categories;  

qryQuotes
Code: SQL  [Select][+][-]
  1. SELECT int_quote_id, txt_quote, int_author_id, int_category_id FROM tbl_quotes WHERE tbl_quotes.int_author_id = :int_author_id AND tbl_quotes.int_category_id = :int_category_id;  

When I try to make qryNationalities and qryQuotes active I'm getting error:
Quote
qryNationalities: Field not found: "int_author_id"

What am I doing wrong?

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Using multiple foreign keys (SQLite)
« Reply #1 on: April 27, 2018, 10:00:11 am »
What am I doing wrong?

Maybe, you could change your schema a little?

Now, table tbl_nationalities is the master and table tbl_authors is its detail.
And table tbl_authors is the master and table tbl_quotes is its detail.
And table tbl_categories is the master and table tbl_quotes is its detail.

Code: SQL  [Select][+][-]
  1.     CREATE TABLE "tbl_nationalities"
  2.     (
  3.             "int_nationality_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
  4.             "txt_nationality" VARCHAR(50) NOT NULL  UNIQUE
  5.     );
  6.      
  7.     CREATE TABLE "tbl_authors"
  8.     (
  9.             "int_author_id" INTEGER PRIMARY KEY  NOT NULL ,
  10.             "txt_author" VARCHAR(100) NOT NULL,
  11.             "int_nationality_id" INTEGER NOT NULL,
  12.             FOREIGN KEY (int_nationality_id) REFERENCES tbl_nationalities(int_nationality_id)
  13.     );
  14.      
  15.     CREATE TABLE "tbl_categories"
  16.     (
  17.             "int_category_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
  18.             "txt_category" VARCHAR(75) NOT NULL  UNIQUE
  19.     );
  20.  
  21.     CREATE TABLE "tbl_quotes"
  22.     (
  23.             "int_author_id" INTEGER NOT NULL,
  24.             "int_quote_id" INTEGER PRIMARY KEY  NOT NULL ,
  25.             "txt_quote" VARCHAR(500) NOT NULL  DEFAULT (NULL)
  26.             "int_category_id" INTEGER NOT NULL,
  27.             FOREIGN KEY (int_author_id) REFERENCES tbl_authors(int_author_id),
  28.             FOREIGN KEY (int_category_id) REFERENCES tbl_categories(int_category_id)
  29.     );

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Using multiple foreign keys (SQLite)
« Reply #2 on: April 27, 2018, 10:06:55 am »
Open them in the following order:

Code: Pascal  [Select][+][-]
  1. qryNationalities.Open;
  2. qryAuthors.Open;
  3. qryCategories.Open;
  4. qryQuotes.Open;

nikel

  • Full Member
  • ***
  • Posts: 186
Re: Using multiple foreign keys (SQLite)
« Reply #3 on: April 28, 2018, 03:01:05 pm »
Hello, I tried the sequence of queries you provided but I'm getting same error. I also tried to change sequence of tables in database however couldn't do it, I'm using SQLite Manager (Firefox addon).

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Using multiple foreign keys (SQLite)
« Reply #4 on: May 01, 2018, 12:37:48 pm »
Please, attach a small sample project.

nikel

  • Full Member
  • ***
  • Posts: 186
Re: Using multiple foreign keys (SQLite)
« Reply #5 on: May 06, 2018, 08:42:36 am »
I removed my post with attachment and pm'ed a file to you.
« Last Edit: May 06, 2018, 11:41:51 am by nikel »

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Using multiple foreign keys (SQLite)
« Reply #6 on: May 07, 2018, 07:06:47 am »
I removed my post with attachment and pm'ed a file to you.

Please, see attachments.
I have made your project to work, but you still need:

- learn more about SQL and SQLite:
http://www.sqlitetutorial.net/
https://www.tutorialspoint.com/sqlite/sqlite_overview.htm

- learn more how FPC and Lazarus deals with databases:
http://wiki.freepascal.org/SqlDBHowto
http://wiki.freepascal.org/Working_With_TSQLQuery

- learn how to deal with lookup:
http://wiki.freepascal.org/lookup
http://wiki.freepascal.org/TDBLookupComboBox

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Using multiple foreign keys (SQLite)
« Reply #7 on: May 07, 2018, 07:17:55 am »
I have deleted your component qryMaster because it is useless.

And I have also changed your qryQuotes.SQL from:
Code: SQL  [Select][+][-]
  1. SELECT
  2.   int_quote_id,
  3.   txt_quote,
  4.   int_author_id,
  5.   int_category_id
  6. FROM
  7.   tbl_quotes
  8. WHERE
  9.   tbl_quotes.int_author_id = :int_author_id
  10.   AND tbl_quotes.int_category_id = :int_category_id;

To:
Code: SQL  [Select][+][-]
  1. SELECT
  2.   Q.int_quote_id,
  3.   Q.int_category_id,
  4.   C.txt_category,
  5.   Q.int_author_id,
  6.   A.txt_author,
  7.   A.int_nationality_id,
  8.   N.txt_nationality,
  9.   Q.txt_quote
  10. FROM tbl_quotes Q
  11. INNER JOIN tbl_categories C
  12.   ON  C.int_category_id = Q.int_category_id
  13. INNER JOIN tbl_authors A
  14.   ON  A.int_author_id = Q.int_author_id
  15. INNER JOIN tbl_nationalities N
  16.   ON  N.int_nationality_id = A.int_nationality_id;

nikel

  • Full Member
  • ***
  • Posts: 186
Re: Using multiple foreign keys (SQLite)
« Reply #8 on: May 08, 2018, 06:40:27 am »
Hello, thanks for your help. I'm still having some difficulties. When user clicks the authors grid I want specific quotes to display. I tried:

Code: Pascal  [Select][+][-]
  1. procedure TMainForm.grdAuthorsCellClick(Column: TColumn);
  2. var
  3.   SQLQuery: TSQLQuery;
  4. begin
  5.   SQLQuery := TSQLQuery.Create(nil);
  6.   SQLQuery.Database := dbConnection;
  7.   SQLQuery.Transaction := transaction;
  8.  
  9.   SQLQuery.Close;
  10.   SQLQuery.Clear;
  11.   SQLQuery.SQL.TEXT:='Select ' +
  12.   'Q.int_quote_id, ' +
  13.   'Q.int_category_id, ' +
  14.   'C.txt_category, ' +
  15.   'Q.int_author_id, ' +
  16.   'A.txt_author, ' +
  17.   'A.int_nationality_id, ' +
  18.   'N.txt_nationality, ' +
  19.   'Q.txt_quote ' +
  20.   'From tbl_quotes Q ' +
  21.   'Inner Join tbl_categories C ' +
  22.   'on  C.int_category_id = Q.int_category_id ' +
  23.   'Inner Join tbl_authors A ' +
  24.   'on  A.int_author_id = Q.int_author_id ' +
  25.   'Inner Join tbl_nationalities N ' +
  26.   'on N.int_nationality_id = A.int_nationality_id;' +
  27.   'WHERE A.int_author_id = Q.int_author_id';
  28.   grdQuotes.DataSource:=SQLQuery.DataSource;
  29.   SQLQuery.Free;
  30. end;

but the grid becomes empty after clicking an authors cell.

GAN

  • Sr. Member
  • ****
  • Posts: 370
Re: Using multiple foreign keys (SQLite)
« Reply #9 on: May 08, 2018, 07:29:13 am »
Quote
but the grid becomes empty after clicking an authors cell.
Yes, because you don't open the query and also you free the query, so the grid always will be empty.
Lazarus 2.0.8 FPC 3.0.4 Linux Mint Mate 19.3
Zeos 7̶.̶2̶.̶6̶ 7.1.3a-stable - Sqlite 3.32.3 - LazReport

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Using multiple foreign keys (SQLite)
« Reply #10 on: May 08, 2018, 08:34:26 am »
... I'm still having some difficulties. ...

Again: You will move faster if you give yourself some time to study the very basic about DB and Lazarus:
https://www.youtube.com/results?search_query=lazarus+sqlquery

How to use Youtube's Auto Caption feature
https://www.youtube.com/watch?v=Y7FDktLN_f8

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Using multiple foreign keys (SQLite)
« Reply #11 on: May 08, 2018, 08:48:07 am »
Hello, thanks for your help. I'm still having some difficulties. When user clicks the authors grid I want specific quotes to display. I tried:

Code: Pascal  [Select][+][-]
  1. procedure TMainForm.grdAuthorsCellClick(Column: TColumn);
  2. var
  3.   SQLQuery: TSQLQuery;
  4. begin
  5.   SQLQuery := TSQLQuery.Create(nil);
  6.   SQLQuery.Database := dbConnection;
  7.   SQLQuery.Transaction := transaction;
  8.  
  9.   SQLQuery.Close;
  10.   SQLQuery.Clear;
  11.   SQLQuery.SQL.TEXT:='Select ' +
  12.   'Q.int_quote_id, ' +
  13.   'Q.int_category_id, ' +
  14.   'C.txt_category, ' +
  15.   'Q.int_author_id, ' +
  16.   'A.txt_author, ' +
  17.   'A.int_nationality_id, ' +
  18.   'N.txt_nationality, ' +
  19.   'Q.txt_quote ' +
  20.   'From tbl_quotes Q ' +
  21.   'Inner Join tbl_categories C ' +
  22.   'on  C.int_category_id = Q.int_category_id ' +
  23.   'Inner Join tbl_authors A ' +
  24.   'on  A.int_author_id = Q.int_author_id ' +
  25.   'Inner Join tbl_nationalities N ' +
  26.   'on N.int_nationality_id = A.int_nationality_id;' +
  27.   'WHERE A.int_author_id = Q.int_author_id';
  28.   grdQuotes.DataSource:=SQLQuery.DataSource;
  29.   SQLQuery.Free;
  30. end;

but the grid becomes empty after clicking an authors cell.

By your tests and doubts, I suggest you to study more tutorials and examples [on SQL language, FPC, Lazarus and SQLdb components] before you try to adventure yourself in your own project.

Try:
Code: [Select]
procedure TMainForm.grdQuotesCellClick(Column: TColumn);
var
  SQLQuery: TSQLQuery;
  sQuote: String;
begin
  SQLQuery := TSQLQuery.Create(nil);
  SQLQuery.Database := dbConnection;
  SQLQuery.Transaction := transaction;

  SQLQuery.Close;
  SQLQuery.Clear;
  SQLQuery.SQL.Add('Select txt_quote From tbl_quotes ');
  SQLQuery.SQL.Add('Where int_quote_id = ' + qryQuotes.FieldByName('int_quote_id').AsString + ';');
  SQLQuery.Open;
  sQuote := SQLQuery.FieldByName('txt_quote').AsString;
  transaction.CommitRetaining;
  SQLQuery.Free;
  ShowMessage(sQuote);
end;

nikel

  • Full Member
  • ***
  • Posts: 186
Re: Using multiple foreign keys (SQLite)
« Reply #12 on: July 07, 2018, 12:54:13 am »
Hi, I'll learn more. You cant expect me to learn these things quickly since I don't have enough programming basics.

I want to ask why the above code can't update dbgrid:


Code: Pascal  [Select][+][-]
  1. procedure TMainForm.grdNationalitiesMouseDown(Sender: TObject;
  2.   Button: TMouseButton; Shift: TShiftState; X, Y: Integer);
  3. var
  4.   SQLQuery: TSQLQuery;
  5. begin
  6.   SQLQuery := TSQLQuery.Create(nil);
  7.   SQLQuery.Database := dbConnection;
  8.   SQLQuery.Transaction := transaction;
  9.   SQLQuery.Close;
  10.   SQLQuery.Clear;
  11.  
  12.   SQLQuery.SQL.Add('SELECT ');
  13.   SQLQuery.SQL.Add('    Q.int_quote_id, ');
  14.   SQLQuery.SQL.Add('    Q.int_category_id, ');
  15.   SQLQuery.SQL.Add('    C.txt_category, ');
  16.   SQLQuery.SQL.Add('    Q.int_author_id, ');
  17.   SQLQuery.SQL.Add('    A.txt_author, ');
  18.   SQLQuery.SQL.Add('    A.int_nationality_id, ');
  19.   SQLQuery.SQL.Add('    N.txt_nationality, ');
  20.   SQLQuery.SQL.Add('    Q.txt_quote ');
  21.   SQLQuery.SQL.Add('FROM tbl_quotes Q ');
  22.   SQLQuery.SQL.Add('LEFT JOIN tbl_categories C ');
  23.   SQLQuery.SQL.Add('    on  C.int_category_id = Q.int_category_id ');
  24.   SQLQuery.SQL.Add('LEFT JOIN tbl_authors A ');
  25.   SQLQuery.SQL.Add('    on  A.int_author_id = Q.int_author_id ');
  26.   SQLQuery.SQL.Add('INNER JOIN tbl_nationalities N ');
  27.   SQLQuery.SQL.Add('    on  N.int_nationality_id = A.int_nationality_id ');
  28.   SQLQuery.SQL.Add('WHERE ');
  29.   SQLQuery.SQL.Add('    A.int_nationality_id = 3;');
  30.   SQLQuery.Active:=True;
  31.   SQLQuery.Open;
  32.  
  33.   dsQuotes.DataSet := SQLQuery;
  34.  
  35.   transaction.CommitRetaining;
  36.   SQLQuery.Free;
  37. end;

The sql works fine in SQLite Manager but when I run program and click the nationalities grid, it clears quotes grid while I'm expecting to select specific records. How can I update dataset and assign it to dbgrid?

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Using multiple foreign keys (SQLite)
« Reply #13 on: July 07, 2018, 01:53:46 am »
Hi, I'll learn more. You cant expect me to learn these things quickly since I don't have enough programming basics.
I want to ask why the above code can't update dbgrid:
The sql works fine in SQLite Manager but when I run program and click the nationalities grid, it clears quotes grid while I'm expecting to select specific records. How can I update dataset and assign it to dbgrid?
Your original project was not designed to have editable grids. Check my Insert Button example. It shows a "manual" insert. You can transform it into a manual update.
You don't need a MouseDown event so far. Start using simple buttons to make your update work, then you can experiment events.
For an editable grid there are some rules you must follow, as you have already studied in Lazarus tutorials. Walk first, fly next. Not the contrary.

 

TinyPortal © 2005-2018