Does every surrogate key have to be a sequential integer?

No. Sequential integers are a common design, including Kimball's dimension-key approach, but the broader concept is an introduced identifier without business meaning.

Does it eliminate the need for business uniqueness rules?

No. Different surrogate values can identify duplicate business records.

Can one entity have several surrogate keys?

Yes, for example when separate dimension records represent its historical versions.

AI Glossary / Data engineering and architecture / Advanced

Surrogate key

Definition

A surrogate key is an identifier introduced by a data system to identify a record independently of its business attributes. It may be assigned through a sequence or another identifier-generation mechanism. Unlike a natural key, its value is not intended to express a business meaning such as a membership number defined by an external process. Generating an identifier and enforcing its uniqueness are separate responsibilities.

What it means

A system often needs a stable way to reference a record even when descriptive information changes. Names, addresses and business codes may be unsuitable as that reference, depending on the domain. A surrogate key provides a separate identity within the system. It does not automatically establish that two records describe different real-world things. Two duplicate customer records can receive different generated identifiers and remain duplicates in business terms. The key identifies the stored record; deciding which person, organization or event that record represents requires additional rules and evidence.

How it works

The system assigns an identifier when it creates a record and uses it in references from related records. Database constraints should enforce the intended identifying property. PostgreSQL, for example, explicitly distinguishes an identity column's automatic value generation from a primary-key or uniqueness constraint. In dimensional warehousing, Kimball's surrogate-key approach also distinguishes historical versions: one source business identifier can correspond to several dimension records over time. The lookup must select the intended version. This warehouse pattern is a specific application of surrogate keys, not a requirement that every operational update create a new record.

Three practical examples

A learning platform assigns an internal learner-record identifier so references do not depend on a person's changeable display name.
A warehouse uses different dimension keys for historical versions of a business entity and links events to the appropriate version.
An integration service retains source identifiers alongside internal identifiers, preserving the mapping needed to investigate imported records.

Think of it this way

Imagine a museum assigning an internal catalog number to an object. The object's description or display location can change without changing that reference. However, assigning two catalog numbers to the same object does not create two different objects. The analogy explains why record identity and real-world identity must be distinguished, and why duplicate detection remains necessary even when every record has its own number.

Worked example

Consider an original fictional warehouse that tracks a learning center's region over time. The source identifier is center-C7. Dimension record 501 represents region North for dates from 1 January up to, but not including, 1 July. Record 734 represents region South from 1 July onward. A June event maps to 501; an August event maps to 734. Both records concern center-C7, but their surrogate keys distinguish the historical versions. There are two version records and one source entity in this example. Joining events only to the source identifier without resolving the applicable interval could match both records or assign the wrong region. The example assumes valid, non-overlapping intervals and known event dates; handling missing or corrected dates requires additional design decisions.

Illustrative case study

In a fictional course-service integration, incoming learner records receive new internal identifiers every time a batch is replayed. The database accepts them because the identifiers differ, but the application now shows repeated learners. Engineers add a defined source-to-internal mapping and a replay rule, then test unchanged repeats and genuine source corrections separately. They also preserve enough provenance to explain how each internal record was created. The scenario illustrates that generated uniqueness does not supply idempotent ingestion or entity resolution. It does not report a measured deployment result or claim that one matching rule is suitable for every organization.

Common misconceptions and mistakes

A surrogate key is not an encryption mechanism, and replacing a name with an identifier does not by itself anonymize a dataset. Another mistake is assuming that automatic generation guarantees uniqueness under every operation: resets, explicit inserts and implementation details matter, so constraints remain important. A larger identifier also does not necessarily mean a later business event. Finally, a historical dimension key identifies a version record rather than necessarily serving as a permanent identifier for the underlying entity across all versions. The intended scope should be documented instead of inferred from the column's name.

Business use

Surrogate keys can simplify references and reduce dependence on changeable source codes, but they introduce a mapping that must be maintained. Define what the identifier represents: an entity, a record, a version or another unit. For AI data preparation, use that distinction when joining features and labels or retrieving historical context. Retain relevant source provenance and test replay, merge and correction scenarios. An identifier that remains unique is useful, yet it does not prove that a join chose the right historical record or that two apparently distinct records represent different subjects. Those are separate quality checks.

Check your understanding

How many dimension records describe center-C7 in the toy example?

Answer: Two.

Which key should an August event use?

Answer: Seven hundred and thirty-four under the stated intervals.

Why did the fictional replay produce duplicates despite unique identifiers?

Answer: Every repeated record received another identifier without being reconciled to the existing source mapping.

What does automatic generation need alongside it?

Answer: Appropriate constraints and application rules for the intended identity.

Related terms

Natural key
Primary key
Identity column
slowly changing dimension
durable key
entity resolution

Learn next

Design a fictional mapping from source identifiers to internal record identifiers. Add a repeated input, a changed description and a historical version. Decide which cases reuse an identifier and which create another, explaining the meaning of each choice. Then trace an event-date lookup and identify what should happen if two version intervals overlap.

Sources

Kimball Group, Dimension Surrogate Keys: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/dimension-surrogate-key/ PostgreSQL documentation, Identity Columns: https://www.postgresql.org/docs/current/ddl-identity-columns.html The center-version example, museum analogy and integration scenario are original educational illustrations. Kimball's dimensional-modeling guidance is scoped to that design context; PostgreSQL provides the concrete distinction between value generation and uniqueness enforcement.

Previous: Surrogate data testing Next: Surrogate loss