Creating a relation

To create a relation you should have already created at least two tables in the current database. In addition, the tables should have at least one field in common. See Joining tables for more information.

Note: It is important that the tables to be related have their join-field, that is the field that is to be common to the tables, set as either a primary key (in the case of the left-hand table) or as a non-unique index or no index (in the case of the right-hand table). See Indexes and primary keys for more information. Indexes can be used to control what data is entered into a field: for instance, allowing no duplicates in a primary key field or as many duplicates as you like in an indexed or no index field. Obviously the use of indexes facilitates a relation between, say, a customer table, which wouldn’t normally contain more than one record per person, and an invoice table, which will often contain several records for the same customer.

Follow these steps to create a relation between two tables:

  1. In Database Manager right-click your mouse and then select New followed by Relation. The "New Relation" dialog is displayed.

  2. In the Relation Structure box you decide which tables are to be joined, which fields are to be used as the join fields, and the type of join to be applied. Each line represents one join from one table to another.

  3. Click in the Left Table field and click on the arrow to display a drop-down list of all the tables in the current database. Click on one of these to select it as the left table. Notice that the large left-hand box, just above the Relation Structure box, now displays all the fields in the table, with the name of the table appearing just above the box.

  4. Next, click in the Join Field and click on the arrow to display a drop-down list of all the fields in the left table. Click on the field that you want to use as the join field.

  5. Click on the arrow in the Right Table field to display a drop-down list of all the tables in the database, excluding the table already selected as the left table. Select one of these as the right table.

  6. Click in the Join Field and select the field from the right table that is to be joined to the join field from the left table. Notice that a connecting line is drawn between the two join fields, from the left table box to the right table box.

  7. Finally, select the type of join from the Join Type box. The default is Inner, with Left outer and Right outer as alternatives.

  8. You have now defined a relation between two tables. Give the relation a name in the Relation Name box and click on OK. You are returned to Database Manager, with the new relation listed under Relations.

  9. Open the relation by double-clicking on the relation name in Database Manager.

The relation table is displayed, along with all the fields from the two tables and the records allowed by the join fields and join type you chose. See Joining tables for more information on types of join.

At this point, if you want to join another table to the new relation, select Relation from the Format menu. This returns you to the "Format Relation" dialog. Notice that the table you chose as Right Table in the first line of the Relation Structure box is selected automatically as Left Table in the second line. Proceed along the line, selecting the table, join field and join type as appropriate.

You can edit the data in a relation table and thereby change the data in the underlying - joined - tables. Column views, sort orders and filters can be applied in exactly the same manner as for ordinary tables (see Queries and Query components).