A surrogate key is frequently a sequential number (e.g. a Sybase or SQL Server "identity column", a PostgreSQL or Informix serial, an Oracle or SQL Server SEQUENCE or a column defined with AUTO_INCREMENT in MySQL).
Some databases provide UUID/GUID as a possible data type for surrogate keys (e.g. PostgreSQL UUID[3] or SQL Server UNIQUEIDENTIFIER[4]).
One table row represents a slice of time holding all the entity's attributes for a defined timespan.
For example, a table EmployeeContracts may hold temporal information to keep track of contracted working hours.
This has the following advantages: Attributes that uniquely identify an entity might change, which might invalidate the suitability of natural keys.
While using several database application development systems, drivers, and object–relational mapping systems, such as Ruby on Rails or 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.
When every table has a uniform surrogate key, some tasks can be easily automated by writing the code in a table-independent way.
It is possible to design key-values that follow a well-known pattern or structure which can be automatically verified.
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.
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.
The values of generated surrogate keys have no relationship to the real-world meaning of the data held in a row.
The need to perform such a check is so common that Microsoft Access actually provides a "Find Unmatched Query" wizard that generates the appropriate SQL after walking the user through a dialog.
This can be mitigated by not exporting surrogate keys, except as transient data (most obviously, in executing applications that have a "live" connection to the database).
Relational databases assume a unique index is applied to a table's primary key.
The remedy to the full table scan is to apply indexes on the identifying attributes, or sets of them.