Beginner5 minDatabase Fundamentals
UpdatedAug 1, 2026
Edit

Primary Key vs Unique Key

CONCEPTS:SQL Indexing

Question Variations

  • "What is the difference between a Primary Key and a Unique constraint?"
  • "Can a table have multiple Unique Keys? Can it have multiple Primary Keys?"
  • "How do Primary Keys and Unique Keys handle NULL values differently?"
  • "Which one is typically used as the target for a Foreign Key relationship?"

Why This Is Asked

This is a fundamental database design question. It tests whether a candidate understands the basic constraints that ensure data integrity and how they differ in their implementation and usage.

Key Concepts

  • Both enforce uniqueness of values in a column or set of columns.
  • Nullability: Primary Keys cannot be NULL; Unique Keys can (usually one NULL, but it depends on the DB).
  • Quantity: One Primary Key per table; multiple Unique Keys allowed.
  • Physical storage: Primary Key is usually the Clustered Index by default.

Question Variations

  • “What is the difference between a Primary Key and a Unique constraint?”
  • “Can a table have multiple Unique Keys? Can it have multiple Primary Keys?”
  • “How do Primary Keys and Unique Keys handle NULL values differently?”
  • “Which one is typically used as the target for a Foreign Key relationship?”

Answers by Technology

+ Add Variant

Expected Answer (PostgreSQL 16)

  • Primary Key: Unique, Not Null. One per table.
  • Unique Constraint: Unique, allows multiple NULLs.

Why It Matters

Choosing the correct constraint ensures data integrity. PostgreSQL’s treatment of NULLs in unique constraints (allowing many) is SQL-standard compliant but can be a surprise to those coming from SQL Server.

SQL Example

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    sku TEXT UNIQUE,          -- Must be unique if present
    internal_code TEXT UNIQUE -- Multiple rows can have NULL internal_code
);

-- How to allow only one NULL (Partial Index)
CREATE UNIQUE INDEX idx_one_null ON products (internal_code) WHERE internal_code IS NOT NULL;

Common Mistakes

  • Assuming Unique means one NULL: In Postgres, multiple NULLs are allowed in a unique column.
  • Surrogate vs Natural Keys: Over-using natural keys that might change.

Follow-up Questions

  • Can a PK be a UUID? (Answer: Yes).
  • Does a Unique constraint create an index? (Answer: Yes, a B-Tree index).

Expected Answer (MySQL 8.4)

  • Primary Key: Unique, Not Null, and determines physical storage (Clustered).
  • Unique Key: Unique, allows multiple NULLs (standard behavior in InnoDB).

Why It Matters

MySQL handles NULLs in Unique keys similarly to PostgreSQL (allowing multiples). This is important for data integrity design where a field like SSN or LicensePlate might be optional but must be unique if present.

SQL Example

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE, -- Multiple users can have NULL email
    username VARCHAR(50) NOT NULL UNIQUE
);

Common Mistakes

  • Confusing PK with Clustered Index: While they are the same by default in InnoDB, they are different logical concepts.
  • NULL Handling: Expecting a UNIQUE constraint to allow only one NULL (this is SQL Server behavior, not MySQL).

Follow-up Questions

  • Can you have multiple Primary Keys? (Answer: No, only one, but it can be composite).
  • What is the difference between a Key and an Index in MySQL? (Answer: In MySQL, KEY is a synonym for INDEX).

Expected Answer (SQL Server 2022)

  • Primary Key: Unique, Not Null. One per table.
  • Unique Constraint: Unique, allows only one NULL.

Why It Matters

SQL Server’s “one NULL only” rule for unique constraints is a common design hurdle. Understanding the difference between PKs and Unique constraints is essential for proper normalization and indexing strategy.

SQL Example

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    SKU VARCHAR(50) UNIQUE,           -- Unique, no NULLs allowed if PK
    InternalCode VARCHAR(50) UNIQUE   -- Only ONE row can have NULL
);

-- How to allow multiple NULLs (Filtered Unique Index)
CREATE UNIQUE NONCLUSTERED INDEX UX_InternalCode 
ON Products(InternalCode) 
WHERE InternalCode IS NOT NULL;

Common Mistakes

  • NULL handling: Forgetting that adding a unique constraint on an optional column only allows one person to have a NULL value in SQL Server.
  • PK choice: Using a volatile column as a Primary Key.

Follow-up Questions

  • How to allow multiple NULLs? (Answer: Filtered Unique Index).
  • Is the PK always clustered? (Answer: No, but it is by default).

Expected Answer (MongoDB 7.0/8.0)

  • Primary Key (_id): Unique, immutable, and automatically generated if missing.
  • Unique Index: Enforces uniqueness on any other field.

Why It Matters

Unique indexes in MongoDB are the primary way to enforce business-level uniqueness (like email addresses) since NoSQL databases lack many of the constraints found in RDBMS.

Example

db.users.createIndex({ email: 1 }, { unique: true });

Common Mistakes

  • Duplicate Nulls: A unique index only allows one document to lack the field (as null is a value). Use sparse: true to allow multiple documents to not have the field.
  • Sharding: Unique constraints on non-shard-key fields are restricted.

Follow-up Questions

  • Can you change the _id of a document? (Answer: No, it is immutable).
  • What is a Sparse Index? (Answer: An index that only includes documents that have the field).

Expected Answer (Cassandra 5.0)

Cassandra does not have a separate “Unique Key” constraint. Uniqueness is strictly tied to the Primary Key.

  • Upsert Behavior: In Cassandra, an INSERT with an existing Primary Key is actually an UPDATE (Upsert). It does not fail with a “Duplicate Key” error unless you use IF NOT EXISTS (Lightweight Transactions).
  • No Unique Secondary Indexes: You cannot create a secondary index that enforces uniqueness across the cluster.

Why It Matters

Data modeling in Cassandra is “Query-Driven.” If you need data to be unique by Email and by UserID, you must create two separate tables: one keyed by UserID and one keyed by Email.

CQL Example

-- Standard Primary Key
CREATE TABLE users_by_email (
    email text PRIMARY KEY,
    user_id uuid,
    name text
);

-- Conditional Insert (LWT - Performance cost)
INSERT INTO users_by_email (email, user_id) 
VALUES ('alice@example.com', uuid()) 
IF NOT EXISTS;

Common Mistakes

  • Expecting Uniqueness Errors: Assuming INSERT will fail if data exists. It will simply overwrite it.
  • Overusing IF NOT EXISTS: Lightweight Transactions (LWT) require multiple round-trips (Paxos) and are much slower than standard writes.

Follow-up Questions

  • What is a Tombstone? (Answer: A marker created when data is deleted).
  • Why are there no unique indexes? (Answer: Enforcing uniqueness in a distributed system requires cluster-wide coordination, which violates Cassandra’s “availability-first” design).