Surrogate Key in SQL and DBMS
Introduction
A Surrogate Key is an artificial identifier created by a system to distinguish one database row from every other row. It usually has no direct business meaning. Values such as customer_id, employee_id, order_id, and product_id are often surrogate keys when the database or application generates them solely for identity.
Suppose a customer has ID 101 and email john@test.com. The email is meaningful to the business and may change. The number 101 does not describe the customer, but it can remain attached to the same row for its entire lifetime. Orders, addresses, payments, and support cases can reference 101 even after the customer's name, email, phone number, or account classification changes.
This stability explains why surrogate keys are common, but using them well requires more than adding an auto-increment column. A surrogate identifier does not enforce natural business uniqueness, guarantee security, or automatically become a Primary Key. This tutorial explains surrogate and natural keys, generation strategies, relationships, indexes, UUIDs, distributed systems, data warehouses, APIs, migrations, testing, and situations in which a surrogate key is unnecessary.
Simple Definition
A Surrogate Key is generated independently of the descriptive facts stored in a row. Its purpose is to provide a unique, stable handle by which the database and related tables can identify that row.
SURROGATE KEY
= SYSTEM-CREATED IDENTIFIER
+ NO REQUIRED BUSINESS MEANING
+ STABLE ROW IDENTITY
The term describes the source and meaning of the key, not a specific SQL data type. A surrogate may be an integer generated from an identity mechanism, a value produced by a sequence, a UUID, or another controlled identifier. What matters is that the key exists because the system needs an identity, rather than because the value already exists in the business domain.
A key can belong to several conceptual categories. An artificial employee_id may be a surrogate key, a candidate key, and the selected Primary Key at the same time. These labels answer different questions: where the value comes from, whether it can identify a row, and which candidate was selected as the principal identity.
Why Use a Surrogate Key?
Business attributes often change. People update email addresses and phone numbers. Companies revise product codes. Governments replace document numbers. Organizations merge and redefine employee numbering schemes. If a mutable business value is also the key referenced by many tables, every change can require coordinated updates across the relationship graph.
A surrogate separates internal row identity from changeable business data. The identifier remains stable while descriptive attributes evolve. Foreign Keys become simpler, joins use a consistent column, and applications do not need to treat one changeable business value as permanent infrastructure.
Surrogate keys are also useful when the natural identity is long, composite, sensitive, unavailable at creation time, or different across source systems. They let the database establish a row before all business identifiers are known. However, they solve only the internal identity problem. The schema must still protect every valid natural candidate key with appropriate UNIQUE and NOT NULL constraints.
Example Without a Surrogate Key
Consider a customer table that uses email as its Primary Key and an orders table that stores the same email as a Foreign Key.
CREATE TABLE customers (
email VARCHAR(150) PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
order_number VARCHAR(30) PRIMARY KEY,
customer_email VARCHAR(150) NOT NULL,
FOREIGN KEY (customer_email) REFERENCES customers (email)
);
If John changes from john@test.com to john@company.com, the relationship value must change wherever it is stored. Cascading updates can manage a well-controlled relational schema, but the old value may also exist in event messages, caches, exports, audit records, integration payloads, or external systems that do not participate in the transaction.
Email may also have comparison and lifecycle complications. It can be reused, normalized differently, or shared according to product policy. These characteristics make it a risky permanent infrastructure key even when the database correctly enforces current uniqueness.
A Stable Surrogate Design
The customer can instead receive a generated identifier, while email remains a business attribute with its own uniqueness rule.
CREATE TABLE customers (
customer_id BIGINT GENERATED ALWAYS AS IDENTITY,
email VARCHAR(150) NOT NULL,
customer_name VARCHAR(100) NOT NULL,
CONSTRAINT pk_customers PRIMARY KEY (customer_id),
CONSTRAINT uq_customers_email UNIQUE (email)
);
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_number VARCHAR(30) NOT NULL UNIQUE,
customer_id BIGINT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
John remains customer 101 when his email changes. Orders continue to reference 101, and the unique constraint prevents a second active customer from taking an email that is already occupied under the declared business rules. The surrogate and natural key work together: one supplies stable internal identity, while the other preserves domain integrity.
Surrogate Key Versus Natural Key
A natural key comes from meaningful domain data. A vehicle identification number, country code, employee number, product SKU, or account reference may be natural when the business already uses it to identify the entity. A surrogate is invented by the information system and normally has no independent business interpretation.
| Natural Key | Surrogate Key |
|---|---|
| Comes from domain data | Created by the system |
| Has business meaning | Has no required business meaning |
| May be mutable or reassigned | Should remain immutable |
| May be long or composite | Often compact and single-column |
| Useful for business lookup | Useful for internal references |
| Examples: SKU, VIN, country code | Examples: product_id, vehicle_id |
The choice is not always either-or. A strong schema often uses a surrogate Primary Key and retains natural candidate keys as alternate Unique Keys. Discarding natural uniqueness because a generated ID exists allows duplicate representations of the same business entity.
Common Surrogate Key Types
Integer identities and UUIDs are the most familiar forms. Integers may be generated by identity columns, auto-increment features, sequences, or application-managed allocation. They are compact and efficient for indexes and joins. UUIDs provide a much larger decentralized identifier space and can be generated before a row reaches the central database.
Other systems use time-sortable distributed identifiers or carefully designed numeric generators. The implementation should be selected for database support, insertion patterns, replication, operational visibility, collision guarantees, storage, and exposure requirements. It should not encode mutable business facts.
Integer examples:
101
102
103
UUID example:
550e8400-e29b-41d4-a716-446655440000
The generated value must be unique in its intended scope and should never be changed to reflect a new status, location, owner, or category.
Identity and Auto-Increment Columns
Database products provide different syntax for generating numeric surrogate keys. MySQL commonly uses AUTO_INCREMENT, SQL Server uses IDENTITY, and modern PostgreSQL supports SQL-style identity columns. Exact options and behavior differ, so migrations should use product-appropriate syntax.
-- MySQL
CREATE TABLE customers (
customer_id BIGINT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL
);
-- SQL Server
CREATE TABLE customers (
customer_id BIGINT IDENTITY(1, 1) PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL
);
-- PostgreSQL identity syntax
CREATE TABLE customers (
customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL
);
Generation and key constraints remain separate concepts. An identity property produces values; a Primary Key or UNIQUE NOT NULL constraint establishes key integrity. Product defaults often make these features convenient to combine, but an automatically generated column is not conceptually a Primary Key merely because its values increase.
Sequences and Generated Numbers
A sequence is a database object that supplies numbers independently of one table. It can support controlled allocation, several tables, batching, caching, or values required before insertion. Sequence syntax and transactional behavior vary by DBMS.
Generated numeric keys should not be expected to be gap-free. Transactions may roll back after obtaining a number, caches may be lost during restart, and concurrent sessions may commit in a different order from allocation. Gaps do not damage identity because the requirement is uniqueness, not perfect counting.
If a business document legally requires consecutive numbering, that is a separate business sequence with its own audit, transaction, and jurisdiction rules. It should not be confused with a technical surrogate key. Trying to reuse deleted IDs or close gaps introduces concurrency risk and can cause an old reference to point to a different entity.
UUID Surrogate Keys
A UUID is useful when identifiers must be generated independently across services, devices, regions, or disconnected clients. A service can create the identifier before database insertion, reducing dependence on one central numeric allocator. UUIDs also make accidental collision extremely unlikely when generated with an appropriate standard mechanism.
CREATE TABLE documents (
document_id UUID PRIMARY KEY,
document_number VARCHAR(40) NOT NULL UNIQUE,
title VARCHAR(200) NOT NULL
);
UUIDs have tradeoffs. They are wider than typical integers, make logs and manual support work harder to read, and can increase index size. Random insertion order may reduce locality in some storage engines. Time-ordered UUID variants or other sortable schemes can improve insertion patterns, but the choice must be supported consistently across languages and database tooling.
A UUID should still be treated as an identifier rather than a secret. If authorization depends only on a caller knowing the UUID, the API is vulnerable to disclosure, logging, forwarding, or accidental exposure.
Surrogate Key, Primary Key, and Candidate Key
Surrogate describes origin: the system invented the value. Primary describes selection: the candidate was chosen as the table's principal identity. Candidate describes capability: the minimal attribute set can uniquely identify every row. These ideas overlap but are not synonyms.
An artificial ID can be generated without being constrained, in which case it is not yet a reliable candidate key. A table can use a natural value as its Primary Key and keep a generated surrogate as a separate Unique Key. More commonly, the surrogate is made PRIMARY KEY, and natural candidates become alternate keys through NOT NULL UNIQUE constraints.
employee_id - surrogate candidate and Primary Key
employee_number - natural candidate and alternate key
email - natural candidate and alternate key
Correct modeling names each role instead of assuming every column ending in _id is automatically a key.
A Surrogate Key Does Not Enforce Business Uniqueness
This is the most important warning. A generated Primary Key guarantees that every physical row has a different generated ID. It does not guarantee that two rows do not describe the same customer, product, employee, or relationship.
customer_id | email | customer_name
------------+-----------------+--------------
101 | john@test.com | John
102 | john@test.com | John
The rows have different surrogate IDs, so the Primary Key accepts them. If email is a mandatory natural candidate key, the schema also needs UNIQUE (email). If uniqueness is tenant-scoped, it may need UNIQUE (tenant_id, email).
Candidate-key analysis must happen before or alongside surrogate-key selection. The generated ID improves internal identity, but it cannot replace understanding what makes one business entity distinct from another.
Stable and Simple Foreign Keys
Surrogates make relationship columns consistent and compact. Instead of repeating a long account number, normalized email, or several business columns in every child table, a child can store one immutable ID.
CREATE TABLE order_items (
order_item_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders (order_id),
FOREIGN KEY (product_id) REFERENCES products (product_id)
);
Smaller key columns generally reduce index width and simplify joins, especially when the alternative is a long or composite natural key. Stability also prevents a business rename from cascading through relationship columns. The benefit is strongest for entities referenced by many tables.
Foreign Keys still need meaningful names, compatible data types, supporting indexes where appropriate, and explicit deletion behavior. A surrogate value simplifies the reference; it does not design the relationship by itself.
Surrogate Keys in One-to-Many Relationships
In a one-to-many relationship, the parent surrogate is copied into each child as a Foreign Key. One customer ID can appear in many orders, while each order references one valid customer.
CREATE TABLE customers (
customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_number VARCHAR(30) NOT NULL UNIQUE
);
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_number VARCHAR(30) NOT NULL UNIQUE,
customer_id BIGINT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
Customer number and order number remain visible business identifiers. The IDs remain internal relationship anchors. This separation allows business numbering formats to evolve without redefining every relationship.
Surrogate Keys in Many-to-Many Relationships
A junction table can use the two parent Foreign Keys as a composite Primary Key, or it can add its own surrogate Primary Key. If it adds a surrogate, it normally still needs a composite Unique Key to prevent duplicate relationships.
CREATE TABLE user_roles (
user_role_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL,
role_id BIGINT NOT NULL,
assigned_at TIMESTAMP NOT NULL,
CONSTRAINT uq_user_role UNIQUE (user_id, role_id),
FOREIGN KEY (user_id) REFERENCES users (user_id),
FOREIGN KEY (role_id) REFERENCES roles (role_id)
);
The surrogate is useful when other tables must reference a particular assignment, when the relationship has a lifecycle, or when ORM and API conventions benefit from a single-column identity. For a simple mapping that has no independent references, PRIMARY KEY (user_id, role_id) may be clearer and sufficient. Adding an ID to every junction table without a use case creates extra columns and indexes without improving the model.
Composite Natural Identity Plus Surrogate Primary Key
Many entities have a natural identity made of several values. An employee number might be unique only within a company. A product code might be unique only within a catalog. A surrogate can keep Foreign Keys simple while a composite Unique Key preserves the actual business rule.
CREATE TABLE employees (
employee_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
company_id BIGINT NOT NULL,
employee_number VARCHAR(30) NOT NULL,
employee_name VARCHAR(100) NOT NULL,
CONSTRAINT uq_company_employee_number
UNIQUE (company_id, employee_number)
);
Other tables reference employee_id, but the database still rejects a repeated employee number inside one company. This pattern avoids carrying a composite Foreign Key everywhere without weakening business integrity.
Integer Surrogate Key Advantages and Limits
Numeric keys are compact, easy to compare, readable in support conversations, and efficient in many B-tree indexes. Monotonically increasing values often have favorable insertion locality. Database-native generation is mature and easy to use for a single authoritative database.
The limitations appear when independent writers need IDs before reaching that database, when databases merge, or when exposing sequential values reveals volume and enables trivial enumeration. Central generation can also become an architectural dependency in highly distributed workflows. None of these limitations automatically disqualifies integers; they simply belong in the decision.
Use a sufficiently large data type based on realistic lifetime volume. Changing a heavily referenced key from a smaller integer type after exhaustion can be expensive. Avoid assigning semantics to numeric ranges, parity, or prefixes, because doing so turns the surrogate into a fragile intelligent key.
Distributed Identity Generation
Distributed systems may create rows in several services or regions without coordinating each insertion through one database. UUIDs, range allocation, database sequences per shard, and time-based distributed ID algorithms are possible strategies. Each has tradeoffs involving ordering, collision risk, clock dependence, storage, observability, and implementation complexity.
Global uniqueness is not always required. A key can be unique within a service while a composite of service and local ID identifies records in a shared warehouse. Conversely, events crossing boundaries benefit from identifiers that cannot collide when datasets are combined.
The generation algorithm should be a platform decision with libraries, tests, and operational guidance. Teams should not invent random concatenation schemes or embed machine addresses and business data without analyzing privacy, predictability, clock rollback, and migration behavior.
Surrogate Keys in APIs
APIs often expose an entity identifier in URLs such as /customers/101. The exposed identifier may be the database surrogate, but it does not have to be. A service can keep an internal numeric key and expose a separate public UUID or opaque reference.
Separating public and internal identity can limit coupling to storage choices and make record enumeration less obvious. It adds another unique value to maintain, so the benefit should justify the complexity. Whether an ID is sequential or random, every request must enforce authentication, authorization, tenant scope, and object-level access.
Do not let clients update surrogate keys. Treat them as immutable identity values. API contracts should also distinguish the technical ID from business references such as customer number or order number, because users may search by business references while relationships and updates operate on stable IDs.
Privacy and Security Considerations
A surrogate can reduce propagation of sensitive natural values. Referencing customer_id is preferable to copying an email address or government identifier into every child table, log, cache key, and URL. This improves data minimization and makes changes easier.
However, a surrogate key is not a security control. Sequential values are predictable, and UUIDs can leak through logs, browser history, analytics, screenshots, or referrals. Possession of an identifier must never grant access by itself. Authorization should verify that the authenticated actor is allowed to view or modify the referenced entity.
Identifiers also need careful handling in deletion and retention workflows. Reusing a deleted ID can connect old references, audit entries, or delayed messages to a new entity. Generated identities should normally remain permanently retired once issued.
Surrogate Keys in Data Warehousing
Data warehouses frequently assign surrogate keys to dimension rows. Source-system business keys may overlap, change, arrive late, or represent different historical versions. A warehouse surrogate provides one internal identity for a particular dimension record.
In a slowly changing dimension, one customer business ID can have several historical rows, each describing the customer during a different effective period. Every version receives a different surrogate key. Fact rows reference the version that was valid when the business event occurred, preserving historical interpretation even after customer attributes change.
customer_key | source_customer_id | city | valid_from | valid_to
-------------+--------------------+---------+------------+----------
5001 | C100 | Dallas | 2025-01-01 | 2026-03-31
5842 | C100 | Austin | 2026-04-01 | NULL
The natural business ID remains C100, while each warehouse version has a distinct surrogate. This is a specialized but important use of artificial identity.
Schema Evolution and System Integration
Surrogate keys reduce the number of database relationships affected when a business identifier changes format or ownership. They also help integrate several source systems that use different identifiers for the same entity. A master record can receive one internal surrogate while a mapping table retains each source-system key.
Identity resolution is still required. A new surrogate should not be created blindly for every incoming record if several source rows represent the same customer. Matching, merge policy, survivorship, and audit history remain business processes. The artificial key gives the reconciled entity a handle; it does not decide which records belong together.
During mergers or migrations, preserve mappings from legacy keys to new surrogates. Delayed events and historical files may continue to use old identifiers long after the current application has moved.
When a Surrogate Key May Be Unnecessary
Not every table needs an artificial ID. A small reference table may have a stable, compact, universally understood natural key. Country codes are a common example when the project's requirements accept the chosen code standard.
CREATE TABLE countries (
country_code CHAR(2) PRIMARY KEY,
country_name VARCHAR(100) NOT NULL UNIQUE
);
A pure junction table can also use its two Foreign Keys as a composite Primary Key when nothing needs to reference the relationship independently.
CREATE TABLE user_roles (
user_id BIGINT NOT NULL,
role_id BIGINT NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY (user_id) REFERENCES users (user_id),
FOREIGN KEY (role_id) REFERENCES roles (role_id)
);
Choose a surrogate because it solves a concrete stability, width, integration, distribution, historical, or tooling problem, not because every table is assumed to need an id column.
Good Surrogate Key Characteristics
A good surrogate is unique in its intended scope, non-NULL, immutable, compact enough for its workload, and generated by a dependable mechanism. It has no business meaning that might need to change. Its data type has sufficient capacity for the entity's lifetime, and every referencing column uses a compatible type.
Generation should be centralized or standardized enough to prevent collisions. Values should not be recycled. Operational tools should display them without losing precision, and serialization formats should preserve them accurately. If a large numeric ID is sent through an environment that cannot represent it exactly, the API may need to serialize it as text.
The key should also have a clear ownership boundary. If several services independently own customer concepts, sharing one database ID across them may create tighter coupling than using explicit external references and mappings.
Common Surrogate Key Mistakes
The most damaging mistake is using a surrogate to hide duplicate business data. Different generated IDs do not make duplicate customers valid. Natural candidate keys still need constraints and a defined normalization policy.
Other mistakes include assuming auto-increment implies Primary Key, allowing the key to change, encoding branch, year, category, or status into the value, and treating sequence numbers as secret. Reusing deleted IDs can attach delayed messages or historical references to the wrong entity. Choosing a key type without enough capacity creates a difficult future migration.
Teams may also expose one database's internal key as a permanent enterprise identifier before deciding who owns that identity. A storage implementation then becomes a cross-system contract. The solution is not to avoid surrogates, but to distinguish internal, public, external, and business identifiers deliberately.
Migration to a Surrogate Key
Converting an existing natural Primary Key requires a staged migration. Add and populate the surrogate column, enforce its uniqueness and non-nullability, and create a new key constraint according to the database's supported procedure. Child tables need corresponding surrogate Foreign Key values, usually populated by joining through the old natural relationship.
During transition, applications may need to support both identifiers. Validate that every child maps to exactly one parent before changing Foreign Keys. Keep a Unique constraint on the former natural key when it remains a business candidate. After code and integrations use the new identity, old relationship columns can be retired carefully.
Large tables require planning for locks, index builds, replication, rollback, and writes occurring during backfill. A surrogate-key migration is a relationship migration, not just an added column.
Testing Surrogate Key Behavior
Verify that inserts generate a non-NULL unique value and that callers cannot accidentally override it when the database owns generation. Test multiple concurrent inserts and bulk-loading paths. Confirm that rollbacks or deleted rows do not cause unsafe identity reuse, while accepting that numeric gaps are normal.
Update mutable business fields and prove that the surrogate and child relationships remain unchanged. Attempt duplicate natural business values and confirm that their separate Unique constraints reject them. Test Foreign Key violations, deletion behavior, serialization, API routing, and values near data-type boundaries.
For distributed generators, test collision resistance assumptions, clock changes where relevant, ordering claims, node configuration, and merging records from different writers. For warehouse dimensions, verify that a new historical version receives a new surrogate while preserving the same source business key.
Surrogate Key Design Workflow
Start by defining what one row represents and identifying every natural candidate key. Evaluate whether those candidates are stable, compact, non-sensitive, always available, and suitable for references. Introduce a surrogate when it provides a real advantage, then choose a generation strategy that fits the database and system architecture.
Define the entity and row grain
-> identify natural candidate keys
-> evaluate stability, width, sensitivity, and availability
-> decide whether a surrogate adds value
-> select integer, UUID, sequence, or another standard strategy
-> enforce the surrogate as an immutable candidate or Primary Key
-> preserve natural keys with UNIQUE and NOT NULL constraints
-> create and index Foreign Keys appropriately
-> test generation, concurrency, updates, deletion, and integration
Document which identifiers are internal, public, business-facing, and source-system-specific. That vocabulary prevents one value from being used for incompatible purposes.
Complete Customer and Order Example
CREATE TABLE customers (
customer_id BIGINT GENERATED ALWAYS AS IDENTITY,
customer_number VARCHAR(30) NOT NULL,
email_normalized VARCHAR(150) NOT NULL,
customer_name VARCHAR(100) NOT NULL,
CONSTRAINT pk_customers PRIMARY KEY (customer_id),
CONSTRAINT uq_customer_number UNIQUE (customer_number),
CONSTRAINT uq_customer_email UNIQUE (email_normalized)
);
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY,
order_number VARCHAR(30) NOT NULL,
customer_id BIGINT NOT NULL,
order_status VARCHAR(20) NOT NULL,
CONSTRAINT pk_orders PRIMARY KEY (order_id),
CONSTRAINT uq_order_number UNIQUE (order_number),
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
);
customer_id and order_id are surrogate Primary Keys. Customer number, normalized email, and order number remain natural alternate keys protected from duplication. The order relationship uses the stable customer surrogate, while users and integrations can search by meaningful business references.
Key Comparison Summary
| Key concept | Main meaning | Typical example |
|---|---|---|
| Surrogate Key | Artificial system-created identifier | customer_id |
| Natural Key | Identifier derived from business data | customer_number |
| Candidate Key | Minimal attribute set capable of row identity | customer_id or mandatory unique email |
| Primary Key | Selected principal candidate | customer_id selected as primary |
| Alternate Key | Candidate not selected as primary | customer_number |
| Unique Key | SQL-enforced non-duplication rule | UNIQUE (email_normalized) |
| Foreign Key | Reference to an eligible parent key | orders.customer_id |
A single column can fit several rows in this table. A generated customer_id can simultaneously be surrogate, candidate, and Primary Key. Classification depends on the question being asked.
Interview Questions and Answers
What is a Surrogate Key? It is an artificial, system-created identifier used to uniquely identify a row without depending on business meaning.
Is every Primary Key a surrogate? No. A Primary Key may be natural or surrogate. Primary describes selection; surrogate describes origin.
Is every surrogate automatically a Primary Key? No. It must be constrained as a candidate and selected as primary. Generation alone does not create a Primary Key.
Why use a surrogate instead of email? A surrogate remains stable when email changes, keeps Foreign Keys compact, and avoids propagating sensitive business data.
Does a surrogate remove the need for Unique constraints? No. Natural business candidate keys must still be enforced to prevent duplicate entities.
Can a surrogate be a UUID? Yes. Surrogate describes meaning, not data type. Integers and UUIDs are both common.
Why can generated numbers contain gaps? Allocation, caching, rollbacks, and concurrency can consume values without committed rows. Identity requires uniqueness, not gap-free numbering.
When might a table not need a surrogate? When a natural key is stable, compact, non-sensitive, and suitable for relationships, or when a simple junction table is fully identified by its Foreign Keys.
Practical Review Checklist
- Define what one row represents before choosing any key.
- Identify natural candidate keys even when using a surrogate.
- Use a stable generation mechanism appropriate to the architecture.
- Give the key sufficient capacity for the entity's lifetime.
- Keep the surrogate immutable and never recycle issued values.
- Do not encode mutable business meaning into the identifier.
- Enforce natural business uniqueness with separate constraints.
- Choose integer or UUID based on measured system requirements.
- Keep Foreign Key data types compatible with parent keys.
- Distinguish internal IDs from public and business identifiers.
- Apply authorization independently of identifier predictability.
- Accept sequence gaps unless a separate legal numbering rule exists.
- Avoid unnecessary surrogate IDs in simple reference or mapping tables.
- Test generation, concurrency, relationships, migration, and deletion.
Key Takeaway
A Surrogate Key is an artificial identifier created to give a database row stable internal identity. Business attributes can change while the surrogate and its relationships remain unchanged. This makes surrogate keys especially useful when natural keys are mutable, long, composite, sensitive, distributed, or unsuitable for widespread Foreign Key references.
SURROGATE KEY
= ARTIFICIAL
+ UNIQUE
+ STABLE
+ NO REQUIRED BUSINESS MEANING
The strongest common pattern combines a surrogate Primary Key with enforced natural Unique Keys. The surrogate supports stable relationships and system evolution; the natural constraints prevent duplicate business entities. Surrogate keys are valuable identity tools, but they complement domain modeling rather than replace it.