Set up Primary Keys

Primary keys are used to relate one table to another and are essential for dimensional data modelling. Each table can have a primary key that uniquely identifies each row. A primary key can consist of one or more fields. Primary key fields display a key icon in the field list, making them easy to identify.

Primary keys provide the following benefits:

• Jet Analytics Data Integration can enforce the primary key constraint — that is, ensure all primary key values are unique.
• Duplicate key values can be avoided in dimension tables when consolidating data from multiple data sources or data areas.

Set up a primary key field

To include a field in the primary key for a table, right-click the field and select Include in Primary Key.

If the data source in the Ingest instance has primary keys defined for its tables, they are applied automatically when the tables are added to a data area. When adding a table using the Add table with field selection option, primary key fields are indicated by a checkmark in the Primary Key column.

Primary key constraints

Primary key constraint behaviour can be set in the table settings of each table using one of the following options.

• Use instance setting (Error): Uses the default constraint setting of the instance.
• Error: Sends the violating row to the Errors view and prevents it from being added to the Valid table.
• Warning: Sends the violating row to the Warnings view but does not remove it from the Valid table.
• None: Takes no action when a violation occurs. Use only for debugging purposes.

The instance-level default is controlled in the Edit Instance menu and follows the same behaviour options described above.

Supernatural keys as primary keys

When a table has more than one field set as a primary key, consolidating those fields into a single Supernatural key is a good approach. This reduces the number of primary key fields to one and gives more control over key generation.

Set up a supernatural key as a primary key

1. Right-click a table that is not in Simple mode and select Add Supernatural key field.
2. In the Business key selection area, select the primary key fields and click Add.
3. Click Add next to the Key store menu.
4. In the Add Key Store dialog, enter a name for the key store that relates to the table, then click OK.
5. Verify that the fields are in the correct order and click OK to create the supernatural key field.
6. Uncheck the two original primary key fields and set the supernatural key as the primary key.