Can a natural key contain more than one column?

Yes. A composite key can combine business attributes whose values together identify a record uniquely.

Is every natural key the primary key?

No. It can remain an alternate unique identifier while a surrogate key is selected as the primary key.

Does a generated code always count as a surrogate key?

No. A code issued and used by the business can be a natural identifier in the database context. What matters is its role and meaning, not whether a computer generated its characters.

AI Glossary / Data engineering and architecture / Intermediate

Natural key

Definition

A natural key identifies a database record using attributes that already have meaning in the business domain. It can be one attribute or a combination, provided the business rules make it a valid unique identifier for the entity being represented. This contrasts with a surrogate key created primarily for database identification, such as an internally assigned sequence number.

What it means

“Natural” does not mean discovered in nature, automatically reliable or impossible to change. A business-issued code can be a natural key because it has a role outside the database table. Its suitability depends on the rules governing that code.

For example, a document number might be unique only within the organization that issued it. The complete key may therefore need both issuer and document number. The designer must define the entity and scope before deciding which attributes identify it. A value that merely looks unique in today's sample is not enough.

How it works

The team identifies candidate business attributes and confirms their uniqueness, required presence and change behavior. It asks who assigns the values, whether they can be reused and how exceptions are handled.

A chosen natural key can become the primary key, or remain an alternate unique identifier while a surrogate key serves as the primary key. Database constraints enforce the declared rules, but the rules themselves must match reality. PostgreSQL, for example, supports primary, unique and foreign-key constraints, including keys made from multiple columns.

Applications need a policy for corrections and changes. If other records reference the natural key, changing it can affect those relationships. A surrogate key can stabilize internal references while the system still enforces the business identifier's appropriate uniqueness and preserves its history.

Three practical examples

1. Supplier catalog: A part is identified by supplier identifier plus the supplier's part number, assuming that combination is unique and not ambiguously reused within the intended scope.

2. Document register: A document uses issuing office, year and sequence number as its business identifier. The combination matters because the sequence restarts each year and different offices use overlapping numbers.

3. Data integration: An import process matches an external system's account code together with its source-system identifier. Treating the account code alone as globally unique could merge unrelated accounts from different sources.

Think of it this way

Think of an address used to find a particular office. It has meaning beyond an internal filing system, but the full address and its scope matter. “Suite 12” is insufficient without the building.

The analogy has limits. Addresses can change or identify a location rather than the organization occupying it. That limitation is useful: a meaningful identifier is suitable only when it identifies the intended entity under clearly understood rules.

Worked example

Suppose two fictional suppliers use part number P17. Supplier Cedar uses it for a valve, while supplier Birch uses it for a filter. A table keyed only by part_number would incorrectly force these distinct records to share an identifier.

The composite natural key (supplier_id, part_number) distinguishes (Cedar, P17) from (Birch, P17). An internal surrogate key could additionally assign these records IDs 501 and 502. The natural-key constraint remains valuable even if the application uses those internal IDs for references.

Now suppose Cedar retires P17 and later reuses it for a different product. The original uniqueness rule is no longer sufficient for a historical product register. The team must revise the model, perhaps by tracking product identity and effective periods. Adding a surrogate key avoids a primary-key collision but does not by itself resolve the business ambiguity.

Illustrative case study

Fictional case study: KeyMeadow merges records from two service platforms. Each platform has unique account numbers, so the team initially matches accounts by number alone. It discovers that account 420 in one platform belongs to a different organization from account 420 in the other.

The team changes its import identifier to include the source system and maintains an explicit mapping to internal account identities. It separately reviews cases where the same real organization appears in both sources.

KeyMeadow learns that unique source records and unique real-world entities are different questions. A correctly scoped natural key can identify a source record without automatically solving cross-system entity resolution.

Common misconceptions and mistakes

A common mistake is choosing a person's name, email address or another familiar attribute without checking uniqueness and lifecycle rules. Meaningful values can be shared, corrected, changed or reassigned.

Another is assuming a surrogate key removes the need for business uniqueness constraints. It can allow duplicate business records unless the relevant rule is enforced separately. Do not treat a natural key as necessarily a single column, and do not assume “primary” and “natural” describe the same property. Primary describes the chosen identification role; natural describes the origin and meaning of the identifying attributes.

Business use

Natural keys help people reconcile records with business documents and external systems. They can make integrations clearer when their scope and lifecycle are stable and documented. Poorly chosen keys, however, can cause mistaken joins, duplicate records or expensive corrections.

For analytics and AI datasets, identity errors can mix one entity's observations with another's. Evaluate key reuse, source scope and historical changes before linking records. Useful checks include duplicate business identifiers, missing key components and unexpected remapping. The objective is dependable identity, not a universal preference for either natural or surrogate keys.

Check your understanding

Two branches issue invoice number 108. Is invoice_number alone a suitable natural key for the combined invoice table?

Answer: No. It lacks the required scope. A combination such as branch identifier and invoice number may work if the business rules guarantee uniqueness and handle reuse appropriately.

Related terms

Learn next

Choose a business identifier and document who issues it, where it is unique, whether it changes and whether it can be reused. Compare those rules with the entity and history your database needs to represent.

Sources

IBM, “What is a primary key?”, natural and surrogate identifiers: https://www.ibm.com/think/topics/primary-key

PostgreSQL documentation, data-definition constraints: https://www.postgresql.org/docs/current/ddl-constraints.html

The sources support identifier roles and enforcement mechanisms. Supplier, invoice and KeyMeadow examples are fictional; each proposed natural key depends on the explicitly stated business assumptions.

Previous: Natural Join Next: Natural language