Step 5: 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 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:

Foreign key caveats

• Case-sensitivity of foreign keys.  By default, most databases treat alphabetic foreign keys as case-sensitive.

• Data type mismatches.  Iron Speed Designer does not check to see if the data type of a foreign key matches the corresponding field in the primary key table.

• Display As for composite (multi-field) foreign keys.  Iron Speed Designer supports composite foreign keys.  However, Iron Speed Designer does not currently support the "Display as" database property for composite keys.

• Self-referential foreign keys.  Iron Speed Designer supports “self referential” foreign keys.  These are foreign key relationships where a key points into the same table.

• Multiple single-column foreign keys pointing to the same table.  Iron Speed Designer supports tables containing two or more foreign keys that point to the same table.

• Multiple single-column foreign keys pointing to different tables.  Iron Speed Designer supports tables containing two or more foreign keys that point different tables.

• Multiple composite foreign keys pointing to different tables.  Iron Speed Designer supports tables containing multiple composite foreign keys that point to different tables.

• Multiple composite foreign keys pointing to the same table.  Iron Speed Designer supports tables containing multiple composite foreign keys that point to the same table.

• Single field participating in multiple foreign keys.  Iron Speed Designer does not currently support fields that participate in multiple foreign key relationships.  Only a single foreign key relationship is permitted for any given field.

• Foreign keys to other databases.  Iron Speed Designer requires foreign keys to point to tables in the same database.

Multiple foreign key use with Display As

Certain features, such as the “Display As” database property and 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 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: