Editing Surrogate key (section)

==Advantages==

===Stability===

Surrogate keys typically do not change while the row exists. This has the following advantages:

* Applications cannot lose their reference to a row in the database (since the identifier does not change).
* The primary or natural key data can always be modified, even with databases that do not support cascading updates across related [[foreign key]]s.

===Requirement changes===
Attributes that uniquely identify an entity might change, which might invalidate the suitability of natural keys. Consider the following example:

:An employee's network user name is chosen as a natural key. Upon merging with another company, new employees must be inserted. Some of the new network user names create conflicts because their user names were generated independently (when the companies were separate).

In these cases, generally a new attribute must be added to the natural key (for example, an ''original_company'' column).
With a surrogate key, only the table that defines the surrogate key must be changed. With natural keys, all tables (and possibly other, related software) that use the natural key will have to change.

Some problem domains do not clearly identify a suitable natural key. Surrogate keys avoid choosing a natural key that might be incorrect.

===Performance===

Surrogate keys tend to be a compact data type, such as a four-byte integer. This allows the database to query the single key column faster than it could multiple columns (which are often text - which is slower still). Furthermore, a non-redundant distribution of keys causes the resulting [[b-tree]] index to be completely balanced. Surrogate keys are also less expensive to join (fewer columns to compare) than [[compound key]].

===Compatibility===

While using several database application development systems, drivers, and [[object–relational mapping]] systems, such as [[Ruby on Rails]] or [[Hibernate (Java)|Hibernate]], it is much easier to use an integer or GUID surrogate keys for every table instead of natural keys in order to support database-system-agnostic operations and object-to-row mapping.

===Uniformity===

When every table has a uniform surrogate key, some tasks can be easily automated by writing the code in a table-independent way.

===Validation===

It is possible to design key-values that follow a well-known pattern or structure which can be automatically verified. For instance, the keys that are intended to be used in some column of some table might be designed to "look differently from" those that are intended to be used in another column or table, thereby simplifying the detection of application errors in which the keys have been misplaced. However, this characteristic of the surrogate keys should never be used to drive any of the logic of the applications themselves, as this would violate the principles of [[database normalization]].

===Simplicity of Relationships===

Surrogate keys simplify the creation of foreign key relationships because they only require a single column (as opposed to composite keys - which require multiple columns). When creating a query on the database, forgetting to include all the columns in a composite foreign key when joining tables can lead to unexpected results in the form of an undesired [[cartesian product]].