---
title: "Surrogate key"
description: "A surrogate key is a system-generated identifier — typically a sequential integer or UUID — assigned to a row that has no business meaning of its own, used as the primary key in dimension and fact tables."
canonical: "https://motherduck.com/glossary/surrogate-key/"
related:
  - title: "DuckLake | MotherDuck Docs"
    url: "https://motherduck.com/docs/concepts/ducklake/"
  - title: "Constraints | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/constraints/"
  - title: "UNPIVOT | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/unpivot/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# Surrogate key

> A surrogate key is a system-generated identifier — typically a sequential integer or UUID — assigned to a row that has no business meaning of its own, used as the primary key in dimension and fact tables.

## Overview

A surrogate key exists purely to identify a row uniquely within a database; it carries no meaning to the business and is never shown to end users. This is in contrast to a natural key (like an email address or a source system's `customer_id`), which comes from the real-world entity itself. In dimensional modeling, dimension tables almost always use surrogate keys as their primary key, while the natural key from the source system is kept as a regular attribute column for traceability.

Surrogate keys solve several problems natural keys create: natural keys can change (an email address, a product SKU), can be reused across source systems with different formats, and can't distinguish between multiple historical versions of the same entity — which matters for slowly changing dimensions, where one customer's `customer_id` might map to several `customer_key` surrogate values over time, one per historical version.

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

### Example

```sql
CREATE SEQUENCE seq_product_key START 1;

CREATE TABLE dim_product (
    product_key  INTEGER PRIMARY KEY DEFAULT nextval('seq_product_key'),  -- surrogate key
    product_id   VARCHAR,   -- natural key from the source system
    product_name VARCHAR,
    category     VARCHAR
);

-- Or with a UUID surrogate key
CREATE TABLE dim_product_uuid (
    product_key  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id   VARCHAR,
    product_name VARCHAR
);
```

Fact tables then reference the surrogate key, not the natural key, when joining to dimensions — which is also what makes SCD Type 2 possible, since each historical version of a dimension row gets its own surrogate key.

### DuckDB angle

DuckDB supports both common surrogate key patterns shown above: `CREATE SEQUENCE` with `nextval()` for compact integer keys, and the `UUID` type with `gen_random_uuid()` for globally unique identifiers that don't require coordinating a single counter — useful when dimension rows are generated by parallel or distributed loading jobs before landing in DuckDB or MotherDuck.