A primary key is a column or set of columns in a table, whose contained values are used to:
- Uniquely identify one and only one record within a table.
- Relate two different tables (perform joins). Often the primary key on one table is joined to the foreign key on another table.
The general rules when designing a primary key are:
- The primary key value must uniquely identify one and only one record within the table. If the rows are not uniquely identified, errors may result such as multiple counting, duplicate rows in a final result set, or unexpected Analytical Engine or cross-tabbing behavior.
- The primary key value must NOT be null. If the table is joined to another table and some rows have a null primary key, those rows will be eliminated from the end result because it is impossible to join null from one table to any value (including null) in another table.
- The primary key value must NOT change. Consult the following Strategy Technical Note for an explanation of why primary keys should not be allowed to change.
- KB8831: Filters do not get updated after making changes to the lookup tables in Strategy.
Besides the rules above that MUST be followed, Strategy recommends the following characteristics of a primary key:
- The primary key should be numeric or date (avoid the use of text data types). Because it takes longer for the database to compare string values than numeric values, using text columns as primary keys is likely to degrade query performance.
- The primary key should be compact (avoid the use of long data types). Shorter data types are faster to join than longer.
- The primary key should contain the fewest possible number of columns (limit the use of compound keys). Compound keys add complexity to SQL.
- The primary key should be dataless (a primary key should not be based on the content of the non-key columns in the same row). If the primary key is based on real-world data, it may need to change at some point in the future, which is an inadvisable data modeling practice. If primary key values are arbitrary identifiers assigned by the ETL (extraction-transformation-loading) process, the data displayed to the user may change in the future but the identifiers never have to.
Surrogate keys may be the solution if these four conditions cannot be met.
