Set Up Primary Key Rules on an Ingest Instance Data Source
Primary keys are an important part of data models — they identify each row in a table uniquely and are referenced as foreign keys in other tables, defining the relationships through which data flows. Designating which field or fields form the primary key for each table is therefore an important task in Jet Analytics Data Integration.
Because an Ingest instance data source may contain many tables configured in a similar way, the Set Up Primary Keys feature lets you create primary key rules that apply to more than one table based on configurable conditions.
Open the Primary Keys window
Right-click the Ingest instance data source and select Set Up Primary Keys.
The Primary Keys window opens, showing all primary key rules currently configured for the data source tables.
To preview the data in a table before configuring rules, select the table and click Preview to open a query window where you can confirm that the intended field values are suitable for use as a primary key.
Add a primary key rule
Select a table from the list and click Add to open the Add Primary Key Rule Setup dialog.
A primary key rule is created by configuring one or more of the following four Conditions to identify the table and column to include or exclude from the primary key:
- Schema
- Table
- Column
- Data type
For the Schema, Table, and Column conditions, select a comparison operator and enter a value to identify the target table and column. The available comparison operators are: Contains, Contains (ignore case), Equals, Equals (ignore case), Not contains, Not contains (ignore case), Not equal, and Not equal (ignore case).
Once the conditions are configured, select one of the following Actions to determine whether the matching fields are included in or excluded from the primary key:
- Include in primary key
- Exclude from primary key
Click OK to create the rule. In the Primary Keys window, click Refresh to see the new rule listed above and the updated primary key indicators in the table list below.
Apply a rule to multiple tables
By setting the Table condition to <All>, a primary key rule can apply to multiple tables that share a similar column naming convention. If some of those tables contain a column that matches the rule but should not be a primary key, create an additional override rule to exclude that column for the specific table.
For example, if multiple tables have a primary key column named ID with a data type of Guid, the following rule would apply to all matching tables:
- Schema: dbo
- Table: <All>
- Column: ID
- Data type: Guid
- Action: Include in primary key
If the Supplier table has an ID field of type Guid that should not be a primary key, create an override rule:
- Schema: dbo
- Table: Supplier
- Column: ID
- Data type: Guid
- Action: Exclude from primary key
After creating the override rule, use the Move Up and Move Down buttons to position the override exclude rule above the include rule so that it is evaluated first.
Create a composite primary key using override rules
If a single primary key rule cannot produce a unique value for every row in a table, additional override rules can be created to combine multiple fields into a composite primary key. Click Override to select additional fields from the table and create supplementary rules.
After selecting the additional field or fields, click OK to create the new rules and return to the Primary Keys window. Use Move Up and Move Down to set the correct evaluation order.
In the example above, the dbo.Products table now has a composite primary key based on both the ProductID and Product_Name fields. Each field has its own rule, and the rules are ordered so that ProductID is evaluated first.