How do I create a master detail SQLITE3 database?
lazarus / 16-Databases

----------------------------------------------------------------------
Robz · 2016-08-23 20:10

Hello,

Will a "join" command work to create a master-detail database?
I can read and write to an sqlite3 database as long as there is no
join connection. There are two tables. A [C] (Cost table) and an [R]
(Recipe table). However when an “inner join” is active, I can no
longer write to the target table [R]. I am trying to create a master-
detail database relationship.

The problem code is:
The master detail "view" is OK; but cannot write.

“select [main].[R].[INGREDIENT      ]
from   [main].[C]
       inner join [main].[R] on [main].[R].[INGREDIENT      ] =
[main].[C].[Ac];”




However this works correct, for writing; but no master detail view.

“select [main].[R].[INGREDIENT      ]
from   [main].[R];”

Is this problem a limitation of SQLITE3 or is there solution? Help is
greatly appreciated.

----------------------------------------------------------------------
tatamata · 2016-12-02 12:47

Don't use multitable quries for records editing! Insert and edit
records in each table separately.

In most cases and in most database engines, queries are read-only,
meant for reporting, not for data editing.

In Lazarus front-end you can create master/detail relashionship
between Datasets and then use master/detail forms for data input.

Also, very important, when using SQLite, you must enable foreign keys
usage. Do that this way:

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
SQLite3Connection1.DatabaseName :=
Filename;SQLite3Connection1.Params.Add('foreign_keys=1'); // or
=onSQLite3Connection1.Connected := True;