Create a Foreign Key
Last updated 22/11/2023
To create a foreign key, you must also create the foreign key field to hold the primary key value used for the referenced table. After you have named your table and added the primary key, perform the following steps to create a foreign key. If you have not added a table in Five before, please refer to the chapter Add a Table.Add a Foreign Key
Prerequisites- Add the data source that you want to create a relationship with.
- Add your table.
- Add your primary key field and index.
Optional
The other fields for your table can be added either before or after foreign key fields.
Add a Foreign Key Field
1. Add your table.2. Click the Fields tab.
Figure 1 - Fields tab
3. Click the Add Fields button.
Figure 2 - Add Fields button
4. Type an ID in the Field ID field.
tip
It's good naming convention to give your field an ID the same as in the foreign table!
6. Optional: click the Required switch.
7. Click the lookup icon in the Default Display Type field and select _Lookup.
info
By selecting the display type to be a lookup, Five will take the first logical field in the referenced table and display the values in the lookup at the form-level.
Figure 3 - Add a foreign key field
8. Click the Save button in the form app bar.
Figure 4 - Save button
9. Click the Indices tab.
Figure 5 - Indices tab
10. Click the Add Indices button.
Figure 6 - Add Indices button
11. Type an ID in the Index ID field.
12. Click the lookup icon in the Key Type field and select Foreign.
13. Click the lookup icon in the Foreign Data Source field and select a data source.
info
Five will automatically populate the Dependent Index field with the primary key for the selected data source.
Figure 7 - Add a foreign index
14. Click the Index Fields tab.
Figure 8 - Index Fields tab
15. Click the Add Index Fields button.
Figure 9 - Add Index Fields button
16. Click the lookup icon in the Field field and select the field on the current table.
Figure 10 - Add an index field
17. Click all three Save buttons in the stacked form app bars.
Figure 11 - Save buttons
18. Click the Save button in the Table Upgrade window.
Figure 12 - Save the table upgrade
Is Cascade Delete
The Is Cascade Delete is an option that can be specified on a foreign key constraint in Five. It allows you to automatically perform corresponding deletion on the related records on the referencing table.For example, suppose you have two tables, Staff and Payment and the Payment table references the StaffKey field on the Staff table. If you want to delete a staff member from the Staff table and also delete any payment records that belong to that staff member, you can use the Is Cascade Delete on the foreign key.
To do this you would turn the Is Cascade Delete switch on, for this to work in your application.
Scenario
The model below shows the relationship between the Staff and the Payment table. The Payment table references the StaffKey on the Staff table.
Figure 13 - Database model
At the form-level, a Staffs form has been created where you can add payments for a staff member. With the Is Cascade Delete turned off, a staff member cannot be deleted while they have payment records attached.
Below, demonstrates a staff record cannot be deleted because the staff member has payment records attached.
Figure 14 - Staffs form
If the Is Cascade Delete is turned on for the StaffKey foreign key on the Payment table, the Staff record can be deleted with payment records attached.
Below, demonstrates a staff record can be deleted with payment records attached because the Is Cascade Delete functionality has been turned on.
Figure 15 - Staffs form deleted
Add Is Cascade Delete
1. Add your foreign index to the table.
Figure 16 - Add the foreign index
2. Click the Is Cascade Delete switch.
Figure 17 - Is Cascade Delete switch
3. Add your index field.
4. Click all three Save buttons in the stacked form app bars.
Figure 18 - Add index field
caution
This feature is not supported when using the
five.executeQuery()
function.Is Parent
The Is Parent is an option that can be specified on a foreign key constraint in Five. It enables you to group related records together in a lookup using the referencing table.For example, suppose you have three tables, Membership, Member, and Gym and the Member table references the GymKey on the Gym table, and the Membership table references the MemberKey on the Member table. If you want to have a Member field on the Membership table that groups the members to their gym, you can use the Is Parent on the foreign key.
To do this you would turn the Is Parent switch on, for this to work in your application.
tip
You can add the Is Parent functionality in the Table Wizard!
Scenario
The model below shows the relationship between the Membership, Member, and Gym tables. The Membership table references the MemberKey on the Member table and the Member table references the GymKey on the Gym table.
Figure 19 - Database model
At the form-level, Gyms, Members, and Memberships forms have been created so you can add gyms, members, and memberships. With the Is Parent switch turned off, all members will be listed in the lookup, however, they will not be grouped by their gym.
Below demonstrates a list of gyms, a list of members, and all members listed in the Members field on the Memberships form. Because the Is Parent switch is turned off for the GymKey foreign key on the Member table that references the Gym table, the members are not grouped by their gym.
Figure 20 - Is Parent off
If the Is Parent switch is turned on for the GymKey foreign key on the Member table, the members will be grouped by their gym in the Members field on the Membership form.
Below, demonstrates the members grouped by their gym because the Is Parent functionality has been turned on.
Figure 21 - Is Parent
Add Is Parent
1. Add your foreign index to the table.
Figure 22 - Add the foreign index
2. Click the Is Parent switch.
Figure 23 - Is Parent switch
3. Add your index field.
4. Click all three Save buttons in the stacked form app bars.
Figure 24 - Add index field