Primary Keys, Alternate Keys, and Secondary Indexes

A key is a logical uniqueness/identity rule; an index is a physical route to rows. Engines often create indexes to enforce keys, but an additional index does not create a new business identifier. This note separates primary and alternate candidate keys from non-unique lookup indexes.

Terminology

Term Meaning Example
Superkey A set of columns that uniquely identifies a row; it can include unnecessary columns. (user_id, email) when user_id alone is unique.
Candidate key A minimal superkey. user_id, or a required unique email.
Primary key The candidate key selected as the table's main identifier. PRIMARY KEY (user_id).
Alternate key A candidate key not selected as the primary key. A required unique email.
Secondary index An additional access path, which can be unique or non-unique. An index on last_name.
Foreign key A constraint connecting referencing values to a permitted referenced key. orders.user_id references users.user_id.

“Secondary key” is used inconsistently: some texts mean an alternate identifier, others mean a search attribute. Prefer the precise terms above.

One schema with two identifiers and a search index

The following runs in PostgreSQL and SQLite. Enable PRAGMA foreign_keys = ON on each SQLite connection when using foreign keys.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    last_name VARCHAR(100) NOT NULL
);

CREATE INDEX idx_users_last_name ON users (last_name);

INSERT INTO users (user_id, email, last_name)
VALUES (1, 'alice@example.com', 'Smith'),
       (2, 'bob@example.com', 'Smith');

Input (users):

user_id email last_name
1 alice@example.com Smith
2 bob@example.com Smith

user_id is the primary key, email is an alternate key, and last_name has a non-unique secondary index. Both Smith rows are valid. Duplicate emails are rejected.

SELECT user_id, email FROM users WHERE last_name = 'Smith' ORDER BY user_id;

Result:

user_id email
1 alice@example.com
2 bob@example.com

The optimizer may use idx_users_last_name or scan the two-row table. The index is intentionally non-unique: two Smiths are valid. The UNIQUE rule on email is a separate integrity requirement; an index alone on last_name never makes the names unique. ORDER BY (not an index's internal order) defines the query result order.

Nullability and composite keys

A primary key is unique and non-null in the relational model. Some legacy SQLite rowid-table declarations permit nulls in primary-key columns; use explicit NOT NULL, INTEGER PRIMARY KEY, or an appropriate strict/without-rowid table to avoid that exception.

A composite key identifies a row by the whole tuple:

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    PRIMARY KEY (student_id, course_id)
);

Neither column must be unique by itself. Nullable UNIQUE columns are not automatically candidate keys: null handling differs by engine, and an identifier should be required.

Keys do not determine one universal storage layout

InnoDB stores rows in its clustered index, normally keyed by the primary key. SQL Server can use a clustered or nonclustered primary-key index. PostgreSQL normally stores table rows in a heap with separate indexes. SQLite's INTEGER PRIMARY KEY aliases the rowid in a rowid table.

Choose identifiers for stability and correct uniqueness rules; choose additional indexes for measured access patterns. Wider keys can increase storage and write costs, especially when secondary indexes also carry the clustered key.