Adding Virtual Foreign Key Relationships

Iron Speed Designer recognizes the foreign key relationships in your database schema and uses them to construct multi-table joins in SQL.  Foreign key relationships specify how tables relate to each other and indicate one-to-many relationships between tables.  For example, your application is able to display Customer and Order Detail information within the Order page using these SQL table joins.

Explicit foreign keys.  If your database contains explicit foreign key relationships, Iron Speed Designer automatically uses them in your application's SQL.

Virtual foreign keys.  You can also inform Iron Speed Designer of implicit foreign key relationships between your database tables that may not be defined in the database.  Iron Speed Designer does the rest, creating appropriate application logic and SQL queries.  In contrast to explicit foreign keys, virtual foreign key relationships are not defined in the database.  Instead, you identify these implicit virtual foreign key relationships to Iron Speed Designer via the Virtual Foreign Key facility.

Iron Speed Designer does not differentiate between Virtual and explicit (real) foreign keys when it creates the underlying database access logic and SQL query statements.  For example, virtual foreign keys are usable for one-to-one and one-to-many relationships and allow editing of multiple related records from either a single or multiple tables.

The only differences between these two foreign keys types are those resulting from the database’s use and enforcement of real foreign keys that are explicitly declared in the database.  This implies:

• Since your application relies on the database to enforce the uniqueness of foreign keys, virtual foreign keys do not have uniqueness enforced.

• Since your application relies on the database to prevent deletion of parent records that still have children, virtual foreign key values do not necessarily reference a valid, existing parent.

• There may be other differences/implications that might affect schema modification, data migration, etc.

You can only create a virtual foreign key to a database table from the same database.  This mimics the behavior of foreign keys defined within most relational databases.

Creating Virtual Foreign Keys

Using Virtual Foreign Keys with Database Views

You can create a virtual foreign key for a database view as long as the database view also has a virtual primary key.  (See Adding Virtual Primary Key Relationships for details on virtual primary keys.)  Since database views themselves are not actual physical tables, you cannot create a foreign key relationship between a database table and a database view.  However, using Iron Speed Designer, you can create a virtual foreign key relationship between a database table and a database view as long as the database view has a virtual primary key.  This virtual primary key provides Iron Speed Designer the information necessary to build applications that can edit, view and delete individual records from the database view.

Creating a Composite Virtual Foreign Key

To create a composite virtual foreign key, first create a virtual foreign key for the first field in the key.  Then, create the second virtual foreign key but select the “Foreign key name” used in the first virtual foreign key field.  Click the Advanced button to access the “Foreign key name”.

See Also

Database and Data Model Enhancement

Adding Virtual Primary Key Relationships

Adding Virtual Foreign Key Relationships

Incorporating Database Schema Changes

Setting Database Field Properties