---
title: "Natural key"
description: "A natural key is an identifier that comes from real-world business data — like an email address, SSN, or product SKU — as opposed to a surrogate key generated purely for database use."
canonical: "https://motherduck.com/glossary/natural-key/"
related:
  - title: "DuckLake | MotherDuck Docs"
    url: "https://motherduck.com/docs/concepts/ducklake/"
  - title: "The Surprising Truth About AI-Native Semantic Layers"
    url: "https://motherduck.com/blog/oops-maybe-we-do-need-semantic-layers/"
  - title: "Constraints | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/constraints/"
---

# Natural key

> A natural key is an identifier that comes from real-world business data — like an email address, SSN, or product SKU — as opposed to a surrogate key generated purely for database use.

## Overview

A natural key (also called a business key) is a column, or set of columns, that uniquely identifies an entity using data that already has meaning outside the database: an email address, a national ID number, a vehicle VIN, a source system's `customer_id`. It's the identifier a business person would recognize and use.

Natural keys are useful and often necessary — they're how you match and deduplicate records coming from a source system, and how you'd look a record up manually. But dimensional models generally avoid using them as the primary key of a dimension table, in favor of surrogate keys, for a few practical reasons: natural keys can change (an employee ID gets reissued, an email address changes), can collide across source systems using different formats or numbering schemes, and can't represent more than one historical version of the same entity, which slowly changing dimensions require.

<glossary-callout guide="duckdb-cheatsheet-full" />

### Example

```sql
CREATE TABLE dim_customer (
    customer_key   INTEGER PRIMARY KEY,   -- surrogate key, used for joins
    customer_id    VARCHAR,               -- natural key from the CRM system
    email          VARCHAR,               -- also a natural/business identifier
    customer_name  VARCHAR
);

-- Look up the surrogate key from the natural key during loading
SELECT customer_key
FROM dim_customer
WHERE customer_id = 'CRM-00219' AND is_current;
```

The natural key is still kept in the dimension table as a regular column — it's essential for matching incoming source records to the correct dimension row during loads, and for tracing a warehouse record back to its system of origin.

### DuckDB angle

Natural keys are ordinary columns in DuckDB with no special type — often given a `UNIQUE` constraint (per current version, for SCD Type 2 tables) rather than `PRIMARY KEY`, since the same natural key can legitimately appear on multiple historical dimension rows.