Step 1e: Explicitly declare foreign keys in your database

Foreign key relationships in a database specify how one table is related to another table.  For example, an Order table may have a Customer ID field that is a foreign key to the Customer table.  The Order ID field indicates which Customer record is associated with the particular order.  It is rare an application has tables that do not contain any foreign key relationships to other tables or views because they would be unconnected ‘orphan’ tables.

Foreign keys can also indicate one-to-many relationships between tables.  For example, an Order table may have a related Order Detail table containing multiple line items for each individual order.  The Order Detail table has an Order ID field that is a foreign key to the Order table.

Iron Speed Designer uses foreign key relationships in order to construct multi-table joins in SQL.  In the examples above, the application is able to display Customer and Order Detail information within a single Order page with table joins in SQL.

Iron Speed Designer uses the foreign key relationships specified in your database to create complex pages with multi-table joins like this one.

Iron Speed Designer determines foreign key relationships by querying the system information in the connected database(s).  If multiple rows are returned for a given foreign key name, it means that the foreign key name is a composite foreign key, i.e. multiple foreign key fields referencing a primary table with a composite primary key. Applications built with Iron Speed Designer support both composite primary and composite foreign keys.

Make sure all foreign key relationships are explicitly defined in your database before creating your application.  This is very important because Iron Speed Designer uses foreign key relationships to create master-detail pages and uses foreign key relationships to determine which related panels to place on a page.  For example, an Orders record page may include an Order Details table panel because of the foreign key relationship between the Order Details table and the Orders table.

Examples:

Many

to

One

OrderDetails.OrderID

à

Orders.OrderID

Orders.ShipperID

à

Shipper.ShipperID

Foreign key caveats
Multiple foreign key use with Display As

Certain features, such as the “Display As” database property and FieldValue dropdown list automatic population, which make use of non-composite (single-column) foreign keys will only use one of the non-composite foreign keys on a particular column, and will ignore any others.  In such cases, Iron Speed Designer designates one of them as “principal” (not to be confused with primary keys).

For example, adding two virtual foreign keys to Northwind.OrderItems.OrderID that point to two different database views of the Northwind.Orders table may not affect the auto-population of FieldValue dropdown list items the way the application user would want.  This is especially true if there is already a real foreign key to the physical Orders table.  If there is an existing physical foreign key in the database, it might be designated as the principal foreign key and be used for “Display As” applications of the field and for dropdown list population.  In this case, the virtual foreign keys will have less of an effect, mostly on code customization and on one-to-many relationships.

Currently, there is currently no way to specifically designate a foreign key as the "principal" foreign key in Iron Speed Designer.

Foreign key limitations

Iron Speed Designer creates applications that use foreign keys of nearly every data type.  All data types are supported as foreign keys except:

Database

Data types NOT supported as foreign key fields

Microsoft Access

Currency
Date/Time
Memo
Hyperlink
OLE Object

Microsoft SQL Server

Bit (Boolean)
Text (Very Large String)

See Also

Step 1a: Create child tables for one-to-many relationships

Step 1b: Create separate lookup tables

Step 1c: Create database views to express multi-table joins and complex queries

Step 1d: Explicitly declare primary keys in your database

Step 1e: Explicitly declare foreign keys in your database

Step 1f: Declare virtual primary keys in Iron Speed Designer

Step 1g: Declare virtual foreign keys in Iron Speed Designer