Candidate, Primary, Alternate, Natural, and Surrogate Keys

Learning outcomes

A relational table needs a reliable way to distinguish one row from every other row. That sounds simple, but real systems often have several plausible identifiers: an email address, a government number, a product code, a generated integer, or a combination of values. This lesson builds the vocabulary required to evaluate those choices precisely.

Why rows need identity

Without a key, two rows with similar values may be impossible to distinguish safely. Updates can affect the wrong record, foreign keys have no dependable target, duplicate facts accumulate, and synchronization becomes ambiguous.

Superkeys and candidate keys

Suppose a table has an attribute set A. A superkey is any subset of attributes whose values uniquely identify each row. A candidate key is a minimal superkey: it is unique, and removing any participating attribute destroys that guarantee.

For a membership table, (organization_id, membership_number) may be a candidate key. Adding full_name creates a superkey, but not a candidate key, because the name is unnecessary for uniqueness.

Primary and alternate keys

After identifying all candidate keys, the designer selects one as the primary key. The remaining candidate keys are alternate keys and should normally still be enforced with UNIQUE plus NOT NULL when their values are mandatory.

PRAGMA foreign_keys = ON;CREATE TABLE member (    member_id          INTEGER PRIMARY KEY,    organization_id    INTEGER NOT NULL,    membership_number  TEXT NOT NULL,    email_address      TEXT NOT NULL,    full_name          TEXT NOT NULL,    UNIQUE (organization_id, membership_number),    UNIQUE (email_address));

Here, member_id is the chosen primary key. The organization-scoped membership number and the email address are alternate keys. Declaring only the surrogate primary key while omitting the two UNIQUE rules would allow business duplicates.

Natural keys

A natural key is derived from the domain: an ISBN, ISO country code, vehicle identification number, or organization-issued membership number. Natural keys communicate meaning and can prevent duplicates at the same boundary where the business recognizes identity.

Strong natural-key characteristics

• the issuing authority and uniqueness scope are explicit;
• the value is available when the row is created;
• it rarely or never changes;
• its format is reasonably compact and validated;
• privacy and security risks are acceptable;
• the value is not reused for a different real-world occurrence.

Natural does not automatically mean good. Email addresses change, names collide, phone numbers are reassigned, and external identifiers may contain sensitive information.

Surrogate keys

A surrogate key is generated for database identity rather than taken from business meaning. Common forms include generated integers and UUIDs. Surrogates are useful when natural candidates are large, composite, mutable, unavailable at creation time, or controlled by external systems.

A surrogate does not remove domain rules. If product_id is generated but sku must be unique, the schema needs both PRIMARY KEY (product_id) and UNIQUE (sku).

Composite keys

A composite key contains more than one column. It is often the clearest identity for association tables and values whose uniqueness is scoped by a parent.

CREATE TABLE course_offering (    course_code   TEXT NOT NULL,    term_code     TEXT NOT NULL,    section_no    INTEGER NOT NULL,    capacity      INTEGER NOT NULL CHECK (capacity > 0),    PRIMARY KEY (course_code, term_code, section_no)) WITHOUT ROWID;INSERT INTO course_offering    (course_code, term_code, section_no, capacity)VALUES    ('SQL-101', '2026-FALL', 1, 30),    ('SQL-101', '2026-FALL', 2, 24);

The combination identifies an offering. None of the individual columns is unique. Composite keys are legitimate, but every referencing table must carry the full key unless a separate surrogate is introduced.

A repeatable key-selection test

A practical pattern is to select a compact surrogate as the primary key while enforcing stable natural candidates as alternate keys. This is not mandatory, but it separates internal references from external identity without sacrificing integrity.

Lab: compare key strategies

Create three tables that deliberately use different identity strategies, then inspect the generated constraints and test duplicate rejection.

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS enrollment;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS country;CREATE TABLE country (    country_code TEXT PRIMARY KEY,    country_name TEXT NOT NULL UNIQUE,    CHECK (length(country_code) = 2)) STRICT;CREATE TABLE product (    product_id INTEGER PRIMARY KEY,    sku        TEXT NOT NULL UNIQUE,    title      TEXT NOT NULL) STRICT;CREATE TABLE enrollment (    learner_id  INTEGER NOT NULL,    offering_id INTEGER NOT NULL,    enrolled_at TEXT NOT NULL,    PRIMARY KEY (learner_id, offering_id)) WITHOUT ROWID;INSERT INTO country VALUES ('DE', 'Germany');INSERT INTO product (sku, title) VALUES ('BDA-SQL-001', 'SQL Workbook');INSERT INTO enrollment VALUES (101, 9001, '2026-08-05T09:00:00Z');SELECT type, name, tbl_name, sqlFROM sqlite_schemaWHERE tbl_name IN ('country', 'product', 'enrollment')ORDER BY tbl_name, type, name;

Common mistakes

Assuming current sample uniqueness proves a key

Five sample rows with different values do not establish a domain guarantee. Key selection comes from requirements and issuing rules.

Using mutable values as foreign-key targets

When identifiers change frequently, every dependent reference must be updated or cascaded. Prefer stable targets.

Adding a surrogate and forgetting alternate uniqueness

This converts duplicate business occurrences into different database rows. Keep the relevant UNIQUE constraints.

Calling every integer ID a surrogate

An externally assigned employee number may be numeric but still natural. Surrogate describes meaning and origin, not data type.

Checkpoint and practice

Summary and next lesson

Keys establish row identity. Candidate keys describe every minimal valid identifier; the primary key is the selected candidate; alternate keys preserve the others; natural keys come from the domain; surrogate keys are generated; and composite keys use several columns. The next lesson uses those keys as targets for foreign-key references.

References

• SQLite: CREATE TABLE constraints
• PostgreSQL: Constraints, unique keys, and primary keys