Entry
Delphi: Database: How to create a simple master-detail link between 2 tables (using primary key)?
Aug 24th, 2003 13:45
Knud van Eeden,
----------------------------------------------------------------------
--- Knud van Eeden - 24 August 2003 - 01:27 am -----------------------
Delphi: Database: How to create a simple master-detail link between 2
tables?
Steps: Overview:
1. -Possible create 2 tables, if they do not already
exist
2. -Open 2 tables
3. -One of this tables is considered the main table.
Let this be table1.
The other table is considered dependent on this
main table.
Let this be table2.
4. -Connect the dependent table to the main table.
So in table2 connect it to table1, via the primary key.
5. -If you now compile and run this, you will only see information in
the dependent table which has the *same* primary key value as in
the main table.
---
Steps: Worked out:
1. -Make sure that when creating the 2 tables:
http://www.faqts.com/knowledge_base/view.phtml/aid/23858/fid/175
1. each table has a primary key defined (*)
e.g. 'Customernumber' is the primary key
in the main table1.
e.g. 'Ordernumber' is the primary key in
the dependent table2.
2. the primary key name of the main table
is included in the names of the
dependent table
Thus in the columns of the dependent table
you have included 'Customernumber'.
3. in the dependent table you have defined
a secundary index.
And you have added in there the name
of this primary key of main table1:
In the list on the right pane, select
'Secundary index'.
Then go to the memo field below that,
and add 'Customernumber' manually.
Then click button 'OK'
4. Save this index with a name of your choice
(e.g. 'mysecundaryindex')
If you should not have done this steps, the master-detail
linking will work, but if you try to add records to the
dependent table, it will give you messages like 'key
violation', 'index not defined' and the like, and or you can
not select the primary key of the main table later on,
and or information is greyed out.
Note:
When you create the tables, you must not forget to
include in all dependent tables a column for
the primary key of the main table (e.g. each
dependent table should also include a 'Customernumber'
column).
Or else it will not work (e.g. master-detail options will be
grayed out).
2. -Open this 2 tables
http://www.faqts.com/knowledge_base/view.phtml/aid/23856/fid/175
First open the main table:
1. Put a table component on the form (from palette 'BDE')
-Change the 'databasename' property to the database (an alias
or a full path filename, or only the filename of the database
if in the same path) containing the first table.
(e.g. 'mydatabasealias', or 'c:\temp\mydatabasename.db')
-change the property 'tablename' to the
name of the first table (e.g. 'mytablename1')
-put the 'Active' property to 'true'
2. Put a 'Datasource' component on the form
(from palette 'Data Access')
-Change the 'datasource' property to 'table1'
3. Put a 'DBGrid' component on the form
(from palette 'Data Controls').
-Change the 'datasource' property to 'datasource1'
4. Put a 'DBNavigator' component on the form
(from palette 'Data Controls').
-Change the 'datasource' property to 'datasource1'
Then open the dependent table:
6. Put a table component on the form (from palette 'BDE')
-Change the 'databasename' property to the database (an alias
or a full path filename, or only the filename of the database
if in the same path) containing your second table
-change the property 'tablename' to the
name of the second table (e.g. 'mytablename2')
-put the 'Active' property to 'true'
7. Put a 'Datasource' component on the form
(from palette 'Data Access')
-Change the 'datasource' property to 'table2'
8. Put a 'DBGrid' component on the form
(from palette 'Data Controls').
-Change the 'datasource' property to 'datasource2'
9. Put a 'DBNavigator' component on the form
(from palette 'Data Controls').
-Change the 'datasource' property to 'datasource2'
Until now you have opened both tables independent of each other.
3. -One of this tables is considered here the main table. Let this be
table1.
The other table is considered dependent on this
main table.
Let this be table2.
You will from now on only have to change the properties of this
dependent table2 component.
4. -Connect the dependent table to the main table.
So in table2 connect it to table1, via the primary key.
In the 'table2' component:
-change the property 'MasterSource' to point to
the datasource component of the main table,
thus choose 'datasource1'
-change the property 'MasterFields' to point to the
primary key of table1 (so it should point to primary key of the
main table).
Click the '...', and the
'Field Link Designer' window will open.
In the 'Detail Fields' pane you should see the name of
the primary key of the main table (e.g. 'Customernumber')
In the 'Master pane' you should see also the name
of the primary key of the main table (e.g. 'Customernumber').
Highlight both, then click the 'Add button'.
This will put them in the pane 'Joined Fields', and show
something like:
'Customernumber -> Customer number'
Click the 'OK' button when ready.
Note: in the 'available indexes' you should see the name
of the 'secundary index' which you chose when creating
the tables (e.g. 'mysecundaryindex')
-change the property 'IndexFieldName' to point to also
the primary key of your main table (e.g. 'Customernumber').
If you are not able to do this (e.g. you see only the
primary key name of the dependent table2, but not the
primary key name of the main table1, use the Borland
Database desktop, menu 'Tools'->'Restructure', to add
a secundary index, as described in the steps above)
http://www.faqts.com/knowledge_base/view.phtml/aid/23910/fid/175
5. -If you now compile and run this, you will only see information in
the dependent table which has the *same* primary key value as in
the main table.
---
Note
the same procedure as above, if you should have 1 main table and 2 or
more dependent tables. You change the properties in the 2 or more
dependent tables to point all to the main table, similar as described
above.
---
Note:
The Borland Database Form wizard can be used to rather automatically
do the above steps and or generate code for it.
[Internet: see also:
http://delphi.about.com/library/weekly/aa022399.htm]
---
[Internet: see also:
http://delphi.about.com/library/howto/htdbmasterdetail.htm]
---
---
An example where you could use the above:
---
Suppose you have 3 girlfriends.
---
And from each of this girlfriends you have a variable amount of
pictures.
---
You want to make a database navigator which when choosen a
particular girlfriend in one table, only shows her pictures
in another table, and not the pictures from the other girls.
---
One brute force method would be to create only one big table with
enough extra columns to put the maximum amount of pictures possible for
a girlfriend in it.
Be this will quite often lead to a table with a possible
very large amount of columns, and a lot of spare cells.
And you do not use a lot of automatism already built in when using the
master-detail, which is specially programmed to cover such
questions.
---
So you possibly could better split your table in 2 separate
tables, one containing only your girlfriendnames, and one containing
only the picturenames.
The connection between both tables you make via a link (e.g. a number
common between both tables).
---
So you could here make then a main table containing the names of your
girlfriends:
1. girlfriendname1
2. girlfriendname2
3. girlfriendname3
---
Let this be table1.
---
You could then make a dependent table, containing the
pictures, with the number of the girlfriend as a link
included.
1. picturefilename1 1
2. picturefilename2 1
3. picturefilename3 1
4. picturefilename4 1
5. picturefilename5 2
6. picturefilename6 2
7. picturefilename7 3
Let this be table2.
---
So in this table2, you see that you have:
4 picturefilenames for first girlfriend
(all pictures with the girlfriendnumber '1' on the right).
Further 2 picturefilenames for the second girlfriend
(all pictures with the girlfriendnumber '2' on the right).
And 1 picturefilename for the third girlfriend
(all pictures with the girlfriendnumber '3' on the right).
---
Use e.g. the Borland Database desktop (you can open this program via
Delphi, menu 'Tools', choose 'Database Desktop) to easily create a
table1 'mygirlfriendstable', with the following fields:
http://www.faqts.com/knowledge_base/view.phtml/aid/23858/fid/175
NAME TYPE SIZE KEY
girlfriendnumber I *
girlfriendname A 50
---
and a table2 'mypicturefilenamestable' with the following fields:
NAME TYPE SIZE KEY
picturenumber I *
girlfriendnumber I
picturefilename A 255
---
So you should have:
1. a primary key in both tables (represented with
the '*' beside 'girlfriendnumber' in table1,
and the '*' beside 'mypicturefilename' in table2).
2. And the primary key of the main table (here 'girlfriendnumber'
is also present in table2.
---
In the pictures table, create a secondary index, by selecting it from
the
listbox on the right.
Then fill in in the textfield the name of the primary key of the
main table.
So fill in 'girlfriendnumber'.
Then press OK.
Save this e.g. as 'mygirlfriendpicturetablesecundaryindex' or any name
of your liking.
---
Possibly create an alias for your database
This will include the path to where your 2 tables are located
(e.g. 'c:\temp')
and a database alias name
(e.g. 'girlfriendpicturedatabase')
The most easy way to create an alias is also to use the Borland
Database desktop for this.
---
Then create a master-detail table link:
---
First open your girlfriend table:
take a table component, and choose the databasename
(e.g. 'girlfriendpicturedatabase')
choose the tablename
(e.g. 'mygirlfriendstable')
put the 'active' component to 'true'
Put a datasource component, which you point to table1.
Put a dbgrid component, which you point to the datasource1
Put a dbnavigator component, which you point to datasource1.
---
Then open your girlfriendpictures table:
take a table component, and choose the databasename
(e.g. 'girlfriendpicturedatabase')
choose the tablename
(e.g. 'mypicturefilenamestable')
put the 'active' component to 'true'
Put a datasource component, which you point to table2.
Put a dbgrid component, which you point to the datasource2
Put a dbnavigator component, which you point to datasource2.
---
From now on you will only change the properties in the
dependent (=girlfriendpictures) table, in order to link both tables.
---
In the pictures table, click on the 'MasterSource' component,
and choose 'datasource1'.
Click on the 'MasterFields', and select the primary key of
the main table (so select 'girlfriendnumber') in
both columns, then click button 'Add'.
In the indexfieldname also select the primary key of the
main table (so select 'girlfriendnumber')
---
To show the pictures, use e.g. a DBImage component
(palette 'Data Access'). The advantage of 'DB...' components
is that they can directly read from your database, so
that you do not have to extra program for that.
Or a usual Image component
(palette 'Additional').
This will show pictures with extension .bmp and .ico.
You will have to include some extra programming, in order
to handle all other relevant file extensions.
http://www.faqts.com/knowledge_base/view.phtml/aid/23902/fid/175
---
After all this, when you navigate in the girlfriendnames table,
you should only see the picturesfilenames
for that particular girlfriend in the other table at that time.
----------------------------------------------------------------------
