What is the difference between a primary key and a unique key?

• Primary key – uniquely identifies every row; exactly one per table; implicitly NOT NULL; the default target for foreign keys; often determines physical clustering order on disk.
• Unique key (unique constraint) – enforces that values are not duplicated; a table can have many; allows NULL in most databases because NULL is treated as "unknown" rather than equal to another NULL.
• Backing index – both are enforced by a unique index, so lookup performance is identical.
• Design convention – the primary key is a stable, meaningless surrogate; natural attributes like email or PAN are unique constraints.

What is a primary key?

A primary key is the chosen identity of a row. There can be only one per table because a row can have only one canonical identity. It is implicitly NOT NULL, because an identity that is unknown is meaningless. Foreign keys from other tables point at the primary key by convention and tooling, and in many databases the primary key also determines the physical clustering order on disk.

CREATE TABLE accounts (
    account_id   VARCHAR(10)  PRIMARY KEY,  -- one per table, never null
    holder_name  VARCHAR(100) NOT NULL,
    email        VARCHAR(100),
    pan          VARCHAR(10),
    balance      DECIMAL(15,2) NOT NULL DEFAULT 0
);

What is a unique key?

A unique key (unique constraint) enforces that no two rows share the same value in the constrained column. A table can have any number of unique keys. Unlike a primary key, a unique column generally permits NULL because the SQL standard treats NULL as "unknown" rather than equal to another NULL.

-- Add unique constraints for email and PAN
ALTER TABLE accounts ADD CONSTRAINT uq_email UNIQUE (email);
ALTER TABLE accounts ADD CONSTRAINT uq_pan   UNIQUE (pan);

-- A new account with no email inserts cleanly:
INSERT INTO accounts (account_id, holder_name, balance)
VALUES ('SAV2099', 'Priya Sharma', 100000.00);
-- email is NULL, which is allowed by the unique constraint.

How do they differ from each other?

| Feature | Primary Key | Unique Key |
|---------|-------------|------------|
| Count per table | Exactly one | Any number |
| NULL allowed | No | Yes (in most databases) |
| Identity semantics | Yes – the canonical row handle | No – just a uniqueness rule |
| Foreign key target | Yes (default) | Possible but uncommon |
| Physical clustering | Often (InnoDB, SQL Server) | No |
| Backing index | Unique index (implicit) | Unique index (implicit) |

-- Both enforce uniqueness and allow fast lookups:
EXPLAIN SELECT * FROM accounts WHERE account_id = 'SAV2001';  -- uses PK index
EXPLAIN SELECT * FROM accounts WHERE email = 'rajesh@nbi.com'; -- uses unique index

When should you use a surrogate primary key?

Even when a natural unique attribute like email exists, teams usually make the primary key a surrogate – an auto-increment or UUID with no business meaning – and keep email as a unique constraint. The primary key should be stable and meaningless, while business attributes change.

-- Surrogate primary key (auto-increment)
CREATE TABLE customers (
    id           BIGSERIAL    PRIMARY KEY,     -- surrogate, stable
    email        VARCHAR(100) UNIQUE,          -- natural, can change
    pan          VARCHAR(10)  UNIQUE,          -- natural, can change
    full_name    VARCHAR(200) NOT NULL
);

If a customer changes their email, the primary key id stays the same, so all foreign keys remain valid. A primary key based on email would require cascading updates everywhere.

What about composite keys?

Both primary keys and unique constraints can span multiple columns. A composite primary key uses two or more columns whose combined values are unique.

-- Composite primary key for a junction table
CREATE TABLE account_holders (
    account_id  VARCHAR(10)  REFERENCES accounts(account_id),
    holder_seq  INT,
    holder_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (account_id, holder_seq)  -- composite PK
);

-- Composite unique constraint
ALTER TABLE accounts ADD CONSTRAINT uq_holder_type
    UNIQUE (holder_name, account_type);
-- Same holder cannot open two accounts of the same type.

What about NULL behaviour in unique constraints?

PostgreSQL permits multiple NULLs in a unique column. MySQL InnoDB also permits multiple NULLs. SQL Server historically allowed only one NULL per unique constraint column, though this has changed in recent versions. This is a point to state carefully in an interview.

-- PostgreSQL: multiple NULLs allowed in unique column
INSERT INTO accounts (account_id, holder_name, email, balance)
VALUES ('SAV2100', 'Anita', NULL, 50000.00);  -- email NULL
INSERT INTO accounts (account_id, holder_name, email, balance)
VALUES ('SAV2101', 'Deepak', NULL, 60000.00); -- email NULL again: allowed!

How do you implement this in Java with JPA?

package com.bank.model;

import jakarta.persistence.*;
import java.math.BigDecimal;

@Entity
@Table(name = "accounts", uniqueConstraints = {
    @UniqueConstraint(name = "uq_email", columnNames = "email"),
    @UniqueConstraint(name = "uq_pan", columnNames = "pan")
})
public class Account {

    @Id
    @Column(name = "account_id", length = 10)
    private String accountId;  // primary key

    @Column(name = "holder_name", nullable = false, length = 100)
    private String holderName;

    @Column(name = "email", length = 100)  // nullable, unique via table constraint
    private String email;

    @Column(name = "pan", length = 10)     // nullable, unique via table constraint
    private String pan;

    @Column(name = "balance", nullable = false, precision = 15, scale = 2)
    private BigDecimal balance;

    // constructors, getters, setters
}

What are common mistakes?

The most common mistake is using a business attribute as the primary key.

-- Wrong: email as primary key (changes when customer updates email)
CREATE TABLE customers (
    email     VARCHAR(100) PRIMARY KEY,  -- if email changes, all FKs break
    full_name VARCHAR(200) NOT NULL
);

-- Correct: surrogate PK, email as unique constraint
CREATE TABLE customers (
    id        BIGSERIAL PRIMARY KEY,
    email     VARCHAR(100) UNIQUE,
    full_name VARCHAR(200) NOT NULL
);

Another mistake is assuming a unique constraint makes a column NOT NULL. It does not – NULL is still allowed unless you add an explicit NOT NULL.

What are common interview follow-up questions?

• "Can a table have no primary key?" Technically yes, but it is bad practice. Every table should have a primary key for identity and performance.
• "Can a foreign key reference a unique constraint instead of the primary key?" Yes, but it is uncommon and requires the referenced column to be NOT NULL and unique.
• "What is a candidate key?" Any column or combination that could serve as the primary key. The one you choose becomes the primary key; the rest become unique constraints.