Primary Keys are used to relate one table to another and essential for dimensional data modeling.

All tables can have a primary key that uniquely identifies each row in the table. The primary key field can consist of one or more fields.

Primary key fields have a “key” image in their icon, helping make them visible in the list of fields within the table. 

Primary keys have the following advantages:

1. TimeXtender Data Integration can enforce the primary key constraint, i.e. that all the primary keys are unique.
2. Duplicate key values can be avoided in your dimension tables when consolidating data from different data sources or data areas.

Content

Set up a Primary Key field

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

If your data source in the Ingest instance has set primary keys for tables, they will be applied automatically when you add them to your data area. If you choose to add a table using the “Add table with field selection option”, then which fields are set as primary keys is indicated by a checkmark in the Primary Key column.

Primary key Constraints

Primary key behavior can be set in the table settings of a table using one of the following options.

1. Use instance setting (error)
   This refers to the default setting of the instance
2. Error
   This will send a row to the Errors view and avoid adding the row to the valid table if there is a violation.
3. Warning
   This will send a row the the Warnings view and not remove the row from the valid table if there is a violation.
4. None
   This will do nothing if a violation occurs. Only use this for debugging.

The instance setting is controlled on the Edit Data Warehouse instance menu.

The behavior is the same as explained above.

Supernatural keys set as primary keys

If you have more than one field in your table that are set as primary keys and you want to limit this to one field creating a Supernatural key consisting of these fields is a good solution.

Set up supernatural key as primary key

1. Start by creating a new supernatural key by right-clicking on a non simple mode table and choosing Add Supernatural key field.
2. Add the primary key fields from the Business key selection area by selecting them and clicking Add.
3. Click on the Add button next to the Key store menu.
4. In the Add Key Store menu, give the store a name that relates to the table.

   

5. Be sure the fields are in the correct order and click OK to create the field.

   

6. Uncheck the two original primary key fields and apply it to the supernatural key.

   

That is it. When the supernatural key is set as primary key it will affect the applied fields so they are treated as primary keys as well.