Surrogate key

As explained in DuckDB in Action

A surrogate primary key is an artificially assigned identifier used as the primary key of a table instead of being derived from business data.

A surrogate key is an artificially assigned primary-key value, used when business data does not provide a reliable unique identifier. Common choices are monotonically increasing numbers and UUIDs. section 3.2.2

Practical design choices include:

• Use an externally generated identifier when the source dataset already supplies stable IDs. section 3.2.2
• Generate numeric identifiers with a database sequence when the table needs its own artificial key. section 3.3.1
• Keep business uniqueness separately constrained; a surrogate key does not replace rules such as unique validity dates. section 3.3.1

Sequence-backed price identifiers

CREATE SEQUENCE IF NOT EXISTS prices_id INCREMENT BY 1 MINVALUE 10; CREATE TABLE IF NOT EXISTS prices ( id INTEGER PRIMARY KEY DEFAULT(nextval('prices_id')), value DECIMAL(5,2) NOT NULL, valid_from DATE NOT NULL, CONSTRAINT prices_uk UNIQUE (valid_from) ); section 3.3.1

Figure 3.1 Energy consumption schema

Use the schema diagram to see how surrogate and referenced keys fit into the energy-consumption tables. section 3.2.2

How DuckDB in Action covers it

How deep it goes

Defines surrogate keys and demonstrates sequence-backed creation, while contrasting them with externally generated and composite keys.

Examples

Demonstrates an externally generated system ID and a sequence-backed price ID in an energy-consumption schema.

What it assumes

Assumes basic tables and primary keys, while clarifying why names and other business attributes may be unsuitable identifiers.

What you can do after

Build it from scratch with SQL sequences, primary-key declarations, and separate business-uniqueness constraints.

How it connects

Connects surrogate keys with primary keys, sequences, UUIDs, foreign keys, composite keys, and unique constraints.

liveBookmanning.com

Read DuckDB in Action on liveBook Get the book at manning.com

Also explained in

• 

  liveBookmanning.com
  Grokking Relational Database DesignA surrogate key is an artificially created key value used to uniquely identify a row independently of the row's natural attributes.
• 

  liveBookmanning.com
  Java Persistence with Spring Data and Hibernate epubA surrogate key is an application-meaningless primary key whose purpose is to identify stored data internally.

Compare how 3 books explain surrogate key →

Related concepts

• composite keyalternative to · A composite key is a key formed from multiple attributes or values whose combination identifies or uniquely distinguishes a record or item.
• primary keyimplements · A primary key is a designated column or attribute, or combination of them, that uniquely identifies each row or item in a table or data store.
• natural keyalternative to · A natural key is an identifier formed from meaningful domain attributes whose values uniquely identify an entity or record.
• business keyalternative to · A business key is one or more domain-meaningful properties whose values identify a business entity or distinguish it for matching and uniqueness.
• @GeneratedValueconfigured by · @GeneratedValue is a Jakarta Persistence annotation that configures automatic generation of an entity identifier according to a specified strategy.

This explainer was generated by Manning's knowledge graph from the text of DuckDB in Action. The section links point to the parts of the book it draws on.