Surrogate key

A surrogate key (alternate key, Eng surrogate key, literally. " Spare key " Key and artificial or synthetic key called ) is a database key in a database table. An important feature of this key is that it is not derived from the data in the table. Surrogate keys are typically generated automatically (eg as a serial number ), often used as a primary key and are used to simplify access to records.

Distinct from natural keys

Unlike a natural key, and ' speaking key' called surrogate keys are generated artificially. The natural key, however, is ( the customer such as the first name, last name and date of birth ) describe intuitively derived from the fields that describe a data object.

Generation

The surrogate key is frequently, but not necessarily, a consecutive number ( sequence number or autonumber ). The key can be assigned either by the database system or by an application program. Traditional applications are here ETL tools for data warehouses.

Known representatives of surrogate keys that are no sequence numbers are the Universally Unique Identifier ( UUID ) and Globally Unique Identifier ( GUID ).

Benefits

The most important property of a substitute key is that it makes a reference to a data element. As opposed to a composite key, only a single field have to be administered as a foreign key.

Another advantage is that when you change a data object, the value of the surrogate key remains unchanged as it has no relationship to the data. Consequently, a change of the foreign key unnecessary.

Finally, it is in practice often not clear which fields form a talking key (or a key that was originally clearly needs to be supplemented later by additional fields due to changed requirements. Soon as there is a customer with the same first name, last name and birthday, for example, be, for example, has the zip code not added ).

Example

The employee database of a company A, the internal employee number is selected as the speaking key. Later come through a merger with another company B added new employees. Their staff numbers collide with numbers of employees from A ( because they were awarded in B before the merger with A). In this case, the key must be changed ( for example by adding an additional field for the origin of the employee ).

A later revision of the key (ie, a change in the list of fields ) but is extremely expensive because it must be in all dependent tables and in all programs that use one of these tables originates.

Disadvantages

If only the surrogate key 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 manage similar tables, can generate the same surrogate key if no precautions are taken against it; in 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. This data is extracted from operational databases and transferred to a star schema. The data is divided into facts and dimensions. The fact tables often include here a large number of foreign keys that refer to the dimension tables. This division is not carried out without a surrogate key in practice.