Table relationships in App Builder

Introduction

Once you have created your app's tables, you can define relationships between them. A relationship links two tables together based on matching data in one or more columns.

Table relationships provide these advantages:

• Relationships let you enforce data integrity at the database level. For example, by linking an Orders table to a Customers table, you can prevent a user from creating an order for a customer that does not exist. You can also define rules that determine what happens to related records when a record is updated or deleted.

• When App Builder knows how tables are related, it automatically enhances the user interface to create a more intuitive experience. For example, when the Order table includes a CustomerID column:

  • Without a relationship, when building a page to create a new order, App Builder generates a simple text box for the CustomerID. The user needs to know and manually enter the correct ID for the customer.

  • With a relationship between the Order table's CustomerID column and the Customer table, App Builder understands the connection. Instead of a text box, it automatically generates a dropdown list or search control on the order page. This control will be populated with the user-friendly names of the customers (from the Customer table's designated Title column), instead of their IDs.

How to create and manage relationships

While tables can be created using the Table Wizard, relationships are managed from the Tables tab within the App Workbench. From there, you can select a table and then define its relationships to other tables. App Builder can often suggest foreign key relationships, which you can then confirm.

When you define a relationship, you can also specify rules that dictate how App Builder maintains data integrity when a record in the parent table (the "one" side of a one-to-many relationship) is updated or deleted. (For example, what should happen to a customer's orders if that customer record is deleted?) These are known as cascade rules. The options are as follows:

• On Delete rule:

  • Cascade: If a parent record is deleted, all its related child records are also deleted. (For example, deleting a customer also deletes all their orders).

  • Default: If a parent record is deleted, the value in the matching foreign key column of the child (referencing) records is set to the column's default value. This action requires that a default value is defined on the foreign key column.

  • Null: If a parent record is deleted, the foreign key value in the related child records is set to NULL. This requires that the foreign key column in the child table allows NULL values. (For example, the orders remain, but are no longer associated with that customer).

  • Prevent: Prevents the parent record from being deleted if it has any associated child records. This is often the safest default option.

• On Update rule:

  • This rule applies if the primary key value of the parent record is changed. The options are the same as for the On Delete rule.

Which rule you choose depends on your business logic and how you want to handle your data.