A relationship is created by linking a field in one table to identical fields in another one. For example in the Customers table each customer has a unique ID number in the field CustomerID. There is also a field in the Orders table called CustomerID. By linking the two and creating a relationship you can display all the orders for a certain customer based on that customer's number.

To create a relationship between two tables click on Tools > Relationships and the following dialogue box will appear (Figure 17).

Click on the Customers table and click the Add button. Click on the Orders table and click on the Add button. Both tables will appear. Click on the CustomerID field in the Customers table and drag it across and drop it on the CustomerID field in the Orders table. A relationship is created between the two tables based on the CustomerID field.

To delete the relationship between the tables, click on Tools > Relationships. Right-click on the line linking the tables (Figure 18) and click on Delete.

You want to make sure that the record between tables remains accurate once a relationship has been created. The way to do this is to enforce referential integrity. Click on Tools > New Relation (Figure 19).

By clicking on Update cascade you are ensuring that if a change is made in the Primary Key field it will make the changes in the other tables that that field is linked to.

Clicking on Delete cascade ensures that Base will delete all the records that are linked to that field. If this option is not selected you will not be allowed to delete a record that is linked to other records.