Surrogate key

from Wikipedia, the free encyclopedia

A surrogate key ( substitute key , English surrogate key literally spare key 'also artificial key or synthetic key called) is a database key that is not derived from the data in the table. Surrogate keys are i. d. Usually formed automatically (e.g. as a sequential number), often used as a primary key and used to make data records easier to access.

Differentiation from natural keys

In contrast to a natural key, also known as a “ speaking key ”, surrogate keys are generated artificially. The natural key, on the other hand, is derived intuitively from the fields that describe a data object (e.g. first name, last name and date of birth describe the customer).

generation

The surrogate key is often, but not mandatory, a consecutive number ( sequence number or auto value ). The key can either be assigned by the database system or by an application program. In the first case, the column is called sequence , auto-increment or identity , depending on the database system . Classic applications here are ETL tools for data warehouses .

Well-known representatives of surrogate keys that are not sequence numbers are the Universally Unique Identifiers (UUIDs) and Globally Unique Identifiers (GUIDs).

advantages

The most important property of a surrogate key is that it simplifies the reference to a data element. In contrast to a composite key, only a single field needs to be managed as a foreign key.

Another advantage is that when a data object is changed, the value of the surrogate key remains unchanged, as it has no relation to the data. Consequently, changing the foreign key is also unnecessary.

After all, in practice it is often not clear which fields form a speaking key (or a key that was originally unique has to be supplemented by additional fields later due to changed requirements. For example, as soon as there is a customer with the same first name, last name and birthday, e.g. the zip code has to be added).

example

In the employee database of company A, the internal employee number is selected as the speaking key. Later, through a merger with another company B, new employees are added. Their employee numbers collide with the numbers of employees from A (because they were assigned in B before the merger with A). In this case, the key must be changed (e.g. by adding another field for the employee's origin).

Changing the key later (changing the list of fields) is extremely time-consuming, however, because it must be reproduced in all dependent tables and in all programs that use one of these tables.

disadvantage

If only the surrogate key is stored in the database, there may be duplicates when inserting or changing.

Another disadvantage is that surrogate keys add an additional field.

Another disadvantage is that separate databases, if they maintain similar tables, can generate identical surrogate keys if no precautions are taken; with a natural key, such a duplicate key would not arise.

Applications

Surrogate keys play an important role in the integration of data into a data warehouse. Here, data is extracted from operational databases and transferred to a star schema . The data is divided into facts and dimensions. The fact tables here often contain a large number of foreign keys that refer to the dimension tables. This division cannot be carried out in practice without a surrogate key.

literature

  • Vinek / Rennert / Tjoa: Data modeling - theory and practice of database design Physica-Verlag, 1982, ISBN 3-7908-0225-5

Web links

Individual evidence

  1. ↑ Surrogate or natural key: How to make the right decision - 2. The primary key should be as compact as possible . In: ZDNet from May 19, 2011
  2. ↑ Surrogate or natural key: How to make the right decision - 3. There can be natural keys with only one field . In: ZDNet from May 19, 2011
  3. ↑ Surrogate or natural key: How to make the right decision - 4. Primary key values ​​should be stable . In: ZDNet from May 19, 2011
  4. ↑ Surrogate or natural key: How to make the right decision - 6. No duplicate entries are allowed . In: ZDNet from May 19, 2011
  5. ↑ Surrogate or Natural Key: How to Make the Right Decision - 8. Surrogate Keys add an unnecessary field . In: ZDNet from May 19, 2011
  6. ↑ Surrogate or natural key: How to make the right decision - 10. Some circumstances seem to require a natural key. In: ZDNet from May 19, 2011