Create a Foreign Key
Last updated 19/04/2024
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.
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.
3. Click the Add Fields button.
4. Type an ID in the Field ID field.
6. Optional: click the Required switch.
7. Click the lookup icon in the Default Display Type field and select _Lookup.
8. Click the Save button in the form app bar.
9. Click the Indices tab.
10. Click the 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.
14. Click the Index Fields tab.
15. Click the Add Index Fields button.
16. Click the lookup icon in the Field field and select the field on the current table.
17. Click all three Save buttons in the stacked form app bars.
18. Click the Save button in the Table Upgrade window.
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.
A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is cascade delete in Five. You can add this functionality when creating your table or editing your 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.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.
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.
Add Is Cascade Delete
1. Add your foreign index to the table.2. Click the Is Cascade Delete switch.
3. Add your index field.
4. Click all three Save buttons in the stacked form app bars.
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.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.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.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.
Add Is Parent
1. Add your foreign index to the table.2. Click the Is Parent switch.
3. Add your index field.
4. Click all three Save buttons in the stacked form app bars.