Definition
- One primary key – a table can declare
PRIMARY KEYonly once, because the primary key is the single canonical identity of a row. - Composite primary key – that one primary key can be made of two or more columns whose combined values are unique, even though no single column is unique on its own.
- When to use composite – junction tables (many-to-many), weak entities (detail rows that depend on a parent, like transaction line items).
- Extra uniqueness – if you need several columns to be independently unique, add
UNIQUEconstraints, not more primary keys. - Surrogate alternative – many teams prefer a single narrow surrogate PK and demote the natural composite to a unique constraint for performance and ORM simplicity.
National Bank of India gives each account exactly one official identity – there cannot be two "official" identities, or the ledger would not know which one is the truth. But that single official identity can be made of more than one piece. For a joint-holding record, the identity might be the pair of account number plus holder sequence, so neither alone is unique but together they are. That is still one primary key, just built from two columns.
Understanding the Concepts
Why can a table have only one primary key?
The reason is conceptual, not arbitrary. The primary key defines the identity of a row – the one handle the database and the rest of the system use to refer to it unambiguously. If a table had two primary keys, there would be two competing notions of identity and the database could not decide which one child tables should reference or which one clusters the data.
-- This is valid: one primary key
CREATE TABLE accounts (
account_id VARCHAR(10) PRIMARY KEY,
balance DECIMAL(15,2) NOT NULL
);
-- This is INVALID: two primary keys
CREATE TABLE accounts (
account_id VARCHAR(10) PRIMARY KEY,
pan VARCHAR(10) PRIMARY KEY -- ERROR: multiple primary keys
);
What is a composite primary key?
A composite primary key is one primary key made of two or more columns. You declare it by listing several columns in one PRIMARY KEY clause. The database enforces uniqueness on the combination.
-- Composite primary key: account_id + holder_seq
CREATE TABLE account_holders (
account_id VARCHAR(10) NOT NULL,
holder_seq INT NOT NULL,
holder_name VARCHAR(100) NOT NULL,
PRIMARY KEY (account_id, holder_seq)
);
-- Valid data:
-- ('SAV2001', 1, 'Rajesh') ← OK
-- ('SAV2001', 2, 'Priya') ← OK (same account, different seq)
-- ('SAV2001', 1, 'Anita') ← ERROR: duplicate (SAV2001, 1)
The same account_id can appear many times and the same holder_seq can appear many times, but the pair must be unique.
When do you use a composite primary key?
Composite primary keys are common in two places:
1. Junction tables (many-to-many): the natural identity is the pair of foreign keys.
CREATE TABLE customer_products (
customer_id BIGINT NOT NULL REFERENCES customers(id),
product_id BIGINT NOT NULL REFERENCES products(id),
enrolled_at TIMESTAMP NOT NULL DEFAULT NOW(),
PRIMARY KEY (customer_id, product_id)
);
2. Weak entities that depend on a parent: the identity includes the parent's key.
CREATE TABLE transaction_line_items (
txn_id BIGINT NOT NULL REFERENCES transactions(txn_id),
line_num INT NOT NULL,
amount DECIMAL(15,2) NOT NULL,
PRIMARY KEY (txn_id, line_num)
);
How do you add extra uniqueness without extra primary keys?
Use unique constraints. A table can have one primary key and as many unique constraints as you need.
CREATE TABLE accounts (
account_id VARCHAR(10) PRIMARY KEY,
email VARCHAR(100) UNIQUE, -- extra uniqueness
pan VARCHAR(10) UNIQUE, -- extra uniqueness
balance DECIMAL(15,2) NOT NULL
);
-- One primary key, two unique constraints.
Why do wide composite primary keys hurt performance?
The primary key is often the clustering key (InnoDB, SQL Server) and is copied into every secondary index entry and every referencing foreign key. A wide composite key (e.g., three VARCHAR(50) columns) inflates index size, foreign key columns, and join costs.
Narrow surrogate PK (BIGINT = 8 bytes):
Every secondary index entry: 8 bytes for PK reference
Every FK column: 8 bytes
Wide composite PK (3 x VARCHAR(50) = up to 150 bytes):
Every secondary index entry: up to 150 bytes for PK reference
Every FK column: 3 columns, up to 150 bytes
This is why many teams prefer a single narrow surrogate primary key.
What is the surrogate vs natural key trade-off?
A surrogate key is an auto-increment or UUID with no business meaning. A natural key uses business attributes.
-- Surrogate PK + natural composite as unique constraint
CREATE TABLE transaction_line_items (
id BIGSERIAL PRIMARY KEY, -- surrogate, narrow, stable
txn_id BIGINT NOT NULL REFERENCES transactions(txn_id),
line_num INT NOT NULL,
amount DECIMAL(15,2) NOT NULL,
UNIQUE (txn_id, line_num) -- natural composite as unique constraint
);
How does a composite primary key work in JPA?
In JPA, a composite primary key requires an embedded id class or an @IdClass annotation, which is more ceremony than a single surrogate @Id.
package com.bank.model;
import jakarta.persistence.*;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Objects;
@Embeddable
public class LineItemId implements Serializable {
@Column(name = "txn_id")
private Long txnId;
@Column(name = "line_num")
private Integer lineNum;
// equals, hashCode required
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof LineItemId that)) return false;
return Objects.equals(txnId, that.txnId) && Objects.equals(lineNum, that.lineNum);
}
@Override
public int hashCode() {
return Objects.hash(txnId, lineNum);
}
}
@Entity
@Table(name = "transaction_line_items")
public class TransactionLineItem {
@EmbeddedId
private LineItemId id;
@Column(name = "amount", precision = 15, scale = 2)
private BigDecimal amount;
}
What are common mistakes?
The most common mistake is confusing "more than one primary key" with "a primary key made of more than one column."
-- Wrong understanding: "I want two primary keys"
PRIMARY KEY (account_id),
PRIMARY KEY (pan) -- ERROR!
-- Correct: one composite primary key
PRIMARY KEY (account_id, holder_seq)
-- Correct: one primary key + one unique constraint
PRIMARY KEY (account_id),
UNIQUE (pan)
What are common interview follow-up questions?
- "Can a composite primary key include a nullable column?" No. All columns in a primary key must be NOT NULL.
- "Should junction tables have a surrogate primary key?" It depends. If the junction table has no additional attributes and is only joined through, a composite PK on the two foreign keys is clean. If it has its own lifecycle, a surrogate PK is better.
- "What is a candidate key?" Any column or combination that could uniquely identify a row. The one you choose becomes the primary key.
Visual Overview
Single-column PK: Composite PK: Two PKs (INVALID):
┌─────────────────┐ ┌───────────────────────┐ ┌─────────────────┐
│ accounts │ │ account_holders │ │ accounts │
├─────────────────┤ ├───────────────────────┤ ├─────────────────┤
│ account_id (PK) │ │ account_id ┐ │ │ account_id (PK) │
│ email (UNIQUE) │ │ holder_seq ┘ (PK) │ │ pan (PK) ← ✗ │
│ pan (UNIQUE) │ │ holder_name │ │ ERROR! │
│ balance │ └───────────────────────┘ └─────────────────┘
└─────────────────┘ │
One PK, two columns
Combined uniqueness
Surrogate vs Composite PK:
┌──────────────────────────────────────────────────────────────┐
│ Surrogate PK (BIGINT) │ Composite PK (3 columns) │
│ - 8 bytes in every index │ - Up to 150 bytes in indexes │
│ - Simple FK references │ - Multi-column FK references │
│ - Easy ORM mapping │ - Requires @EmbeddedId in JPA │
└──────────────────────────────────────────────────────────────┘
Key Takeaway
No, a table cannot have more than one primary key, and the reason is conceptual. The primary key is the single canonical identity of a row – the one handle the database and the rest of the system use to refer to it unambiguously. Two primary keys would mean two competing identities, and the database could not decide which one child tables reference or which one clusters the data.
What trips people up is the difference between more than one primary key and a primary key made of more than one column. The second is completely normal and is called a composite primary key: you list several columns in one PRIMARY KEY clause, and the database enforces uniqueness on the combination. This is the right design for junction tables, identified by the pair of foreign keys, and for weak entities like transaction line items identified by the pair of transaction id and line number.
When you need several columns to be independently unique, you add unique constraints, not more primary keys. A table can have one primary key plus as many unique constraints as needed. The senior nuance is that wide composite primary keys hurt performance because the PK is copied into every secondary index and every referencing foreign key, so many teams prefer a single narrow surrogate primary key and demote the natural combination to a unique constraint. At National Bank of India, transaction line items use a composite primary key on transaction id plus line number because a line has no identity outside its transaction.
Remember one sentence: exactly one primary key per table, but it may be composite, and any extra uniqueness comes from unique constraints, never from additional primary keys.
---