Documentation

First Child Table

Now it is time to add the "customer" column to our orders table.

The Foreign Key Syntax

The "customer" column may only have a value that is the customers table. There are a variety of ways to say this in tech-jargon, depending on what kind of shop you come from it may be simply a [[foreign_key]], or we say that orders is a child table to customers, or we say that orders.customer has referential integrity to customers.customer. They all mean the same thing for our purposes.

We could simply do a [[column placement]] of customers into the orders table, like so:

table orders_h:
   module: orders
   description: Orders
   
   column order_num:
      uisearch: "Y"
      primary_key: "Y"
      automation_id: SEQUENCE
   column customers:       
      uisearch: "Y"

table orders_h:
   module: orders
   description: Orders
   
   column order_num:
      uisearch: "Y"
      primary_key: "Y"
      automation_id: SEQUENCE
   foreign_key customers:
      uisearch: "Y"

which has the following effects:

• The column 'customer' appears magically in the orders table. What is actually happening is that all of the primary key column of the named table appear in the child table. In this case that means the customer column.
• The resulting customer column is indexed for faster lookups.
• Referential Integrity is automatically applied, you can only enter valid customers in the customer column of the orders table.
• The user interface "knows" about the foreign key and reflects it in various ways.

The Database Table

If we look at the database structure, we see that the customer table is there, safe and sound:

The User Interface

There are two basic effects of the foreign key on the user interface. The first is that an HTML SELECT appears on the orders screen when entering a customer:

There is also a drilldown-like link that appears on the customers screen, which will automatically display all orders for one customer: