There are three distinct types of keys that affect the presentation of your database in Gatsby Database Explorer.
| Name | Description |
|---|---|
| Primary Keys | A field or set of fields that uniquely identify a row. |
| Alternate Keys | A user-friendly set of fields that uniquely identify a row. |
| Foreign Keys | A set of fields that reference the primary key fields of another row, usually in another table. |
A primary key is the field or set of fields that can uniquely identify a record. A compound primary key is composed on more than one field.
For your tables it is best to define your primary keys at the database level. Although not strictly necessary, you will get the best results if each table in your database has an auto-increment or identity type primary key field. For views, queries and the results of stored procedures there is no mechanism for defining the primary key at the database level. However, you can specify one through the View properties using the Gatsby Design Assistant.
It is necessary for your database objects to have a primary key to allow navigation to the Form view of a particular record of a view (table, query, view or stored procedure result). The primary key must be non-compound.
Like a primary key, an alternate key is an index or constraint composed of one or more fields that uniquely identify each record. The difference between the primary key and the alternate key is that the alternate key will contain fields that are more meaningful to the user. For instance a table that contains information about departments in a company, called Departments, may have a primary key field called "Id" that is an auto-increment field and another field called "Name" that is the name of the department. The "Id" is created automatically when the department is added to the table and probably does not have any real meaning to the user. The user will uniquely identify the department by its name. However, the "Id" field serves as an efficient way for other tables to reference departments. It is smaller than the name and if the name were to ever change it would not be necessary to update all of the tables that reference the department. It is important to define a unique index or constraint on the "Name" field to prevent duplicate department names from being entered.
Alternate keys are used by Gatsby Database Explorer as a way to determine what appears in a drop-down list box when the table is referenced as a foreign key. In the Gatsby Design Assistant this is called a "Row Caption".
A foreign key is when one table references another table. A foreign key is what connects your tables and views to each other and allows Gatsby Database Explorer to provide a seamless navigation experience through your database. Fields that are defined as foreign keys will be exposed in the Form view as drop-down list boxes.
It is best define your foreign keys at the database level using relationships. In some situations this is not possible. In those cases you can define the foreign key using the Gatsby Design Assistant.
There are number of settings in the Gatsby Design Assistant that affect how your foreign key is presented in Gatsby Database Explorer. All of the settings are available on the Key To tab of the property for the attribute that you want to set up as a foreign key.
First, you can specify which table or view that attribute references using the Key To option. This list only allows you to select tables or views that have a primary key that is of a type that is compatible with the attributes type.
Second, you can define a lookup. This specifies a view where the options for the drop-down list will be drawn. This is useful for situations where you want to limit the items available in the drop-down list. You can specify a lookup without specifying a Key To. This has the effect of creating a drop-down list with out the option of navigating to the foreign view.
The first two options affect how the field is exposed on the Form view of the attributes' parent view. The Related Item options determine how the relationship will be exposed on the foreign view. If you choose not to show the relationship at all on the foreign view, provide a user-friendly name for the relationship and specify whether the relationship should be treated as dependent.