Keys

Keys

Let’s discuss “keys” in the context of data; there are a lot of keys 🙂!

Primary Key (PK)

The primary key is the implementation of the unicity integrity in a table part of the physical data model.

The primary key can consist of more than one attribute. The advice is to define one attribute of an integer data type as the primary key, because that improves performance of the database management systems and it simplifies the definition of relationships with other tables of the physical data model.

Alternate (Unique) Key

The alternate (unique) key is a unique key - other than the primary key - in a table part of the physical data model.

The alternate (unique) key can consist of more than one attribute and it can be defined as a unique index on top of the table. In a few cases more than one alternate (unique) key can be defined on the same table.

The creation of an alternate (unique) key on top of a table is a method to secure the content of the table to be unambiguous.

Synonyms are Candidate Key and Secondary Key.

Foreign Key (FK)

The foreign key is the implementation of the referential integrity in a table part of the physical data model. The foreign key refers to the primary key of another table of the physical data model.

More than one attribute is part of the foreign key, when the primary key of the other table consists of more than one attribute.

The foreign key represents the “Many” in a One-To-Many relationship, or the second “One” in a One-To-One relationship.

Role Playing (Foreign) Key

The role playing foreign key is the implementation of multiple references to the same table part of the physical data model. The name of the foreign key attribute will contain the description of the “Role Play”.

You will encounter role playing foreign keys most at dates, for instance OrderDate, ShipDate, etc.

Concatenated Key

The concatenated key contains more than one attribute of a table. The concatenated key can be a primary key, an alternate key or a foreign key.

The concatenated key may or may not be unique, depending on its functional use.

A synonym is Composed Key.

System Key

The system key is the primary key of a table part of a system’s physical data model.

Business Key

The business key is a unique key of a table, which value is meaningful to the Business and which value is set, controlled and maintained by the Business. In some cases the business key is also the system key, for instance Cost Center Code.

Natural Key

The natural key refers to the primary key of a table part of a system’s physical data model.

The natural key can be the system key and in some cases also the business key.

The natural key is an important key in an Enterprise Data Warehouse solution.

Surrogate Key

The surrogate key is the primary key of a data warehouse table, which contains a history of value changes of attributes of an entity.

The surrogate key is of integer data type.

The primary alternate unique key in a data warehouse table is the concatenation of the natural key and the startdate of the valid period.

The second alternate unique key in a data warehouse table is the concatenation of the natural key and version number.

Hash Key

The hash key is a system calculated value, based on a hashing algorithm and the values of a list of attributes. SQL Server has the HASHBYTES function for this purpose.

The hash key is of a fixed length, so from a mathematical point of view it cannot be unique for all possible inputs. But all such hash functions are carefully designed to minimize the probability of a collision; see Using Hash Codes as Unique Identifiers.

Hash keys are often implemented in data warehouse solutions to be able to identify changes on record level as soon as possible.

Because it is not guaranteed that the calculated values will be unique, it is not recommended to store and use these values as unique keys (in a data warehouse solution). And yet this seems to be common practice in data warehouse solutions?