8.5 Relations

Relations are used to link tables together through common information.

8.5.1 Primary Key

8.5.2 Add a Relation

8.5.3 Using Relations

8.5.4 Delete a Relation

8.5.1 Primary Key

A relation can only be created between a foreign key and a primary key. To specify a field to be a primary key, go to the "Primary Key" page which is accessed by selecting  View/Change | Modify Primary Key  on the "Fields" page. Select the field from the list box and click "Set Primary Key". Note that there can only be one primary field in a table. The Primary Key is indicated by a "P" in the second column of the field information on the "Fields" page. All primary keys (see section 8.3.6 - Required fields) are required fields.

The type of a primary field cannot be changed. The primary key can be changed by choosing another field to be the primary key but the primary key can only be changed if it is not being used by any relations.

A primary key is removed by choosing "None" from the list box and then clicking "Set Primary Key".

8.5.2 Add a relation

To add a relation to the database, select  View/Change  |  View and Edit Relations  from the top level database view. Note that at least one table in the database must have a primary key set on it before it is possible to add a relation to the database. To create a (one-to-many) relation between two fields, the foreign key field (the many) should be chosen and then the corresponding primary key (the one). Once both fields have been chosen, the relation should be created by clicking "Create relation". Note that the data type of the primary and foreign fields must be identical in order that a relation can be made. The Relations page of the relational database is shown in Figure 8.5-1:

Figure 8.5-1: The Relations page

Relations can only be created between fields in different tables and with the same datatype. If adding the relation fails, the user is presented with an error message describing the nature of the error. Note that once a relation exists between two fields, the fields' attributes cannot be changed.

The new relation will be added to the list of Existing Relations at the bottom of the "Relations" page. At this point, the user can prescribe an additional field to display along with the primary field when records are added. This is primarily of use when Automatic IDs are used as primary keys to ensure uniqueness; the possible entries for foreign fields are presented as a list of primary field entries, which may not be particularly intuitive. Thus, an additional display field can be chosen to be displayed alongside the primary field (separated by a colon).

To not show any additional display field, the Display field should be set to the Primary field (this is the default setting).

An example of using this additional display field within a link table is shown in Figure 8.5-2 , where we display elements of a database which uses the relations illustrated in Figure 8.5-1. Three tables are present; 'Patient', 'Medicine' and 'Link'. Patients and medicines are assigned a unique ID (the primary key), and 'prescriptions' are stored by adding records to the 'Link' table; both fields are foreign fields linked to the two primary keys of the other two tables. This is clearly indicated by the Existing Relations shown in Figure 8.5-1, where we have prescribed the Name field as the extra Display field in each case. This field is then added to the drop-down lists presented in the Add Record form for the 'Link' table (Figure 8.5-2). Note that the Display field is NOT added to the actual record in the 'Link' table.

Figure 8.5-2: Relations Example

8.5.3 Using relations

Once two fields have been linked, it is possible to move from one table to the related table by clicking on the icon beside the name of the related field in either table. This provides a quick method of navigating between tables.

Each record in a primary key, which is linked to an entry in the related foreign key displays a icon. Clicking on this icon for a record with value X, say, will take the user to the related table and display all the records in this table which take the value X in the related foreign key field. These records are displayed as a subset of the records in the table.

Each record in a foreign key will display a icon. Clicking on this icon for a record with value Y, say, will take the user to the related primary table and display the record with the value Y in the related primary key. This is a single record and its contents are displayed along with an icon, which allows the user to edit this record.

Note that changing an entry in a primary key will automatically change all the related entries in the foreign key. It is therefore often useful to construct utility tables containing data sets which may change in the future (e.g. categories). If these are used as primary keys, this will reduce the amount of editing potentially needed in future.

8.5.4 Delete a relation

To delete an existing relation, select " Relation Delete " in the database relation view. After selecting the relation to be deleted, click on "Remove Selected" to delete the relation.

Note that relations between tables must be removed before the tables themselves are deleted. Similarly, a relation cannot be deleted if any query uses it.