science

Surrogate key

Surrogate key is a science topic covered in the lgStudy science library. This page brings together a partial reference excerpt, illustrations, worked examples, real-world applications and a short study plan, so you can understand Surrogate key rather than just read about it. In short: A surrogate key (or synthetic key, pseudokey, entity identifier, factless key, or technical key) in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data, unlike a natural (or business) key.

Key takeaways

Surrogate key belongs to science; place it in that map before memorising details.
Learn the definition first, then one example that makes the definition concrete.
Connect Surrogate key to a quantity you can measure, compute or draw — that is where exam questions come from.
Reproduce the core statement of Surrogate key from memory before moving on to harder problems.

Reference excerpt

A surrogate key (or synthetic key, pseudokey, entity identifier, factless key, or technical key) in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data, unlike a natural (or business) key.

Definition There are at least two definitions of a surrogate:

Surrogate (1) – Hall, Owlett and Todd (1976) A surrogate represents an entity in the outside world. The surrogate is internally generated by the system but is nevertheless visible to the user or application. Surrogate (2) – Wieringa and De Jonge (1991) A surrogate represents an object in the database itself. The surrogate is internally generated by the system and is invisible to the user or application. The Surrogate (1) definition relates to a data model rather than a storage model and is used throughout this article. See Date (1998). An important distinction between a surrogate and a primary key depends on whether the database is a current database or a temporal database. Since a current database stores only currently valid data, there is a one-to-one correspondence between a surrogate in the modeled world and the primary key of the database. In this case the surrogate may be used as a primary key, resulting in the term surrogate key. In a temporal database, however, there is a many-to-one relationship between primary keys and the surrogate. Since there may be several objects in the database corresponding to a single surrogate, we cannot use the surrogate as a primary key; another attribute is required, in addition to the surrogate, to uniquely identify each object. Although Hall et al. (1976) say nothing about this, others have argued that a surrogate should have the following characteristics:

the value is never reused the value is system generated the value is not manipulable by the user or application the value contains no semantic meaning the value is not visible to the user or application the value is not composed of several values from different domains.

Surrogates in practice In a current database, the surrogate key can be the primary key, generated by the database management system and not derived from any application data in the database. The only significance of the surrogate key is to act as the primary key. It is also possible that the surrogate key exists in addition to the database-generated UUID (for example, an HR number for each employee other than the UUID of each employee). 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 or SQL Server UNIQUEIDENTIFIER). Having the key independent of all other columns insulates the database relationships from changes in data values or database design (making the database more agile) and guarantees uniqueness. In a temporal database, it is necessary to distinguish between the surrogate key and the business key. Every row would have both a business key and a surrogate key. The surrogate key identifies one unique row in the database, the business key identifies one unique entity of the modeled world. One table row represents a slice of time holding all the entity's attributes for a defined timespan. Those slices depict the whole lifespan of one business entity. For example, a table EmployeeContracts may hold temporal information to keep track of contracted working hours. The business key for one contract will be identical (non-unique) in both rows however the surrogate key for each row is unique.

Some database designers use surrogate keys systematically regardless of the suitability of other candidate keys, while others will use a key already present in the data, if there is one. Some of the alternate names ("system-generated key") describe the way of generating new surrogate values rather than the nature of the surrogate concept. Approaches to generating surrogates include:

Universally Unique Identifiers (UUIDs) Globally Unique Identifiers (GUIDs) Object Identifiers (OIDs) Sybase or SQL Server identity column IDENTITY OR IDENTITY(n,n) Oracle SEQUENCE, or GENERATED AS IDENTITY (starting from version 12.1) SQL Server SEQUENCE (starting from SQL Server 2012) PostgreSQL or IBM Informix SERIAL MySQL AUTO_INCREMENT SQLite INTEGER PRIMARY KEY (if AUTOINCREMENT is used it will prevent the reuse of numbers that have already been used but are available) AutoNumber data type in Microsoft Access AS IDENTITY GENERATED BY DEFAULT in IBM Db2 and PostgreSQL. Identity column (implemented in DDL) in Teradata Table Sequence when the sequence is calculated by a procedure and a sequence table with fields: id, sequenceName, sequenceValue and incrementValue

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 keys.

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.

… excerpt ends here. Continue reading the full article.

Worked examples

Example 1 — a first encounter with Surrogate key

Start with the simplest possible case. Write down what Surrogate key claims or describes in one sentence, then invent the smallest concrete situation in which that sentence is true. In science, the smallest case is usually a single object, a single equation or a single measurement. Check that every symbol or term in your sentence has a meaning in that case.

Example 2 — changing one variable

Take the situation from Example 1 and change exactly one quantity: double it, halve it, or set it to zero. Predict what should happen to Surrogate key before you calculate. Comparing your prediction with the result is the fastest way to find out whether you understand the idea or only the words.

Example 3 — an exam-style question

Typical questions about Surrogate key ask you to (a) state it precisely, (b) apply it to given data, and (c) explain a limitation. Practise writing all three answers in under five minutes; the third part is what separates a full-mark answer from an average one.

Applications of Surrogate key

In research: Surrogate key appears in science research whenever the underlying quantities have to be modelled precisely. Papers usually cite it as a starting assumption and then explore where it breaks down.
In technology and industry: Engineering practice reuses Surrogate key in design rules, simulations and safety margins. Knowing the idea lets you read a specification sheet and understand why the numbers look the way they do.
In the classroom: Surrogate key is common in secondary-school and first-year university syllabi. It links to neighbouring topics Data modeling, Database management systems, so understanding it makes those chapters shorter.
In everyday life: Look for Surrogate key outside the textbook — in sport, cooking, traffic, electronics or the sky above you. An example you found yourself is remembered far longer than one you were given.

Ask Teacher Smith questions about this articleOpens your AI tutor with a question about “Surrogate key” →

Affiliate

Preply — study more efficiently by working with a personal tutor. 50% off.

How to study Surrogate key in 20 minutes

Read the reference excerpt below once, without taking notes.
Close the page and write down what Surrogate key means in your own words.
Compare your version with the excerpt and mark what you missed.
Work through the three examples above with pen and paper.
Explain Surrogate key out loud to somebody else — or to Teacher Smith in the lgStudy chat.

Frequently asked questions

What is Surrogate key in simple terms?

Why does Surrogate key matter?

Because it connects several science ideas at once: it gives you a definition you can apply, a quantity you can calculate, and a way to check whether a result is plausible.

How should I study Surrogate key?

Read the excerpt, restate it from memory, then work through the examples and applications listed on this page. The five-step study plan above takes about twenty minutes.

What does this page cover?

It gives you a compact reference excerpt plus original lgStudy explanations, examples, applications and study material on Surrogate key.