SQL PRIMARY KEY vs UNIQUE KEY

SQL PRIMARY KEY and UNIQUE KEY are two of the most important database constraints because both deal with uniqueness, but they are not the same thing.

That is exactly why people confuse them.

At first glance, they seem almost identical:

• both prevent duplicate values
• both are used in schema design
• both matter for data integrity
• and both often create an index behind the scenes depending on the database

But the moment you start building real tables, the difference matters a lot.

For example:

• which column should identify the row itself?
• should email be the primary key, or just unique?
• can multiple UNIQUE KEY constraints exist in one table?
• what happens with NULL values?
• which key should foreign keys point to?
• and how do you model business uniqueness versus database identity?

Those are practical design questions, not academic ones.

This guide explains SQL PRIMARY KEY vs UNIQUE KEY clearly, including:

• what each one means
• how they differ
• how NULL behaves
• when to use each one
• common schema patterns
• and the mistakes developers make most often

Why this distinction matters

A lot of database design quality comes down to answering two different questions correctly:

1. What uniquely identifies this row as a row?

That is usually a PRIMARY KEY question.

2. What values must be unique because of business rules?

That is often a UNIQUE KEY question.

Those are related questions, but they are not the same.

For example, in a users table:

• user_id may be the primary key
• email may need to be unique
• username may also need to be unique

That means:

• one PRIMARY KEY
• multiple UNIQUE KEY constraints

This is very common in well-designed schemas.

If you mix these roles up, you can create:

• awkward foreign key design
• fragile row identity
• unnecessary update pain
• or tables where business rules are not enforced clearly enough

That is why this topic matters so much.

The most important rule

Before anything else, remember this:

A PRIMARY KEY answers “what row is this?”, while a UNIQUE KEY answers “what values must not repeat?”.

That is the single most useful way to remember the difference.

A PRIMARY KEY is about:

• row identity

A UNIQUE KEY is about:

• uniqueness rules

Sometimes one column can satisfy both roles. But very often they are different.

That is why strong schema design usually treats them as distinct concepts, even though both enforce uniqueness.

What a PRIMARY KEY is

A PRIMARY KEY is the main constraint used to identify each row in a table.

That means:

• every row must have one primary key value
• the value must be unique
• the value must not be NULL

In practical terms, the PRIMARY KEY is the row’s identity.

Example:

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(100) NOT NULL
);

Here:

• user_id is the PRIMARY KEY
• it identifies each user row

That means no two rows can share the same user_id, and no row can have user_id = NULL.

What a UNIQUE KEY is

A UNIQUE KEY is a constraint that says:

• values in this column, or this combination of columns, must not repeat

But unlike a PRIMARY KEY, it is not necessarily the main row identity.

Example:

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

Here:

• user_id is the PRIMARY KEY
• email is unique
• username is unique

That means:

• each row is still identified by user_id
• but the database also prevents duplicate email and duplicate username

This is one of the most common real-world schema patterns.

The simplest difference

A simple side-by-side summary is:

PRIMARY KEY

• uniquely identifies the row
• cannot be NULL
• normally only one per table

UNIQUE KEY

• enforces uniqueness on a column or column set
• may allow NULL depending on the database
• a table can usually have multiple UNIQUE KEY constraints

That is the practical core difference.

Why PRIMARY KEY is usually the main row identity

The PRIMARY KEY usually becomes the value used for:

• foreign key references
• API identity
• row lookups
• update targeting
• deletion targeting
• and entity relationship design

For example:

users

• user_id is the primary key

orders

• user_id may appear as a foreign key referencing users.user_id

That is natural because the primary key is the row’s official identity.

This is why many systems use:

• integer IDs
• bigint IDs
• UUIDs

as PRIMARY KEY values

even when other columns like email are also unique.

Why UNIQUE KEY is often a business rule

UNIQUE KEY is often the right tool for columns like:

• email
• username
• invoice_number
• serial_number
• employee_code
• sku
• tenant-specific external_id
• or combinations like (organization_id, email)

These are not always the best row identity for the table itself. But they still need uniqueness enforced.

That is what UNIQUE KEY is for.

It protects business rules like:

• one email per user
• one username per account
• one invoice number per tenant
• one employee code per company

That is different from saying:

• this is the main identity of the row forever

That distinction is important.

Example: users table

A very common user table design looks like this:

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL
);

This design says:

• user_id is the row identity
• email must be unique
• username must be unique

This is a strong design because:

• foreign keys can reference a stable numeric ID
• email and username still get enforced as unique business values

That is often better than trying to make email the PRIMARY KEY.

Why email is usually better as UNIQUE, not PRIMARY KEY

At first, some people think:

• email is unique, so why not make it the primary key?

The problem is that a primary key should ideally be:

• stable
• compact
• good for relationships
• and reliable as long-term identity

Email is unique, but it may not be ideal as the row’s main identity because:

• users can change email addresses
• email strings are longer than numeric IDs
• foreign keys referencing email are often less convenient
• and business logic may treat email as important but still editable

That is why many schemas choose:

• user_id as PRIMARY KEY
• email as UNIQUE KEY

That is one of the most common and practical examples of the difference.

Can a table have multiple PRIMARY KEY constraints?

Normally, a table has only one PRIMARY KEY.

That is because the primary key is meant to be:

• the main identity of the row

However, that one PRIMARY KEY can be:

• a single column
• or a composite key made of multiple columns

Example of a composite primary key:

CREATE TABLE enrollment (
    student_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,
    enrolled_at TIMESTAMP NOT NULL,
    PRIMARY KEY (student_id, course_id)
);

This table still has:

• one PRIMARY KEY

But that key consists of:

• two columns together

That is different from having multiple separate primary keys.

Can a table have multiple UNIQUE KEY constraints?

Yes. This is one of the biggest differences from PRIMARY KEY.

A table can have multiple UNIQUE KEY constraints because it may need to enforce multiple different uniqueness rules.

Example:

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(100) NOT NULL UNIQUE,
    national_id VARCHAR(50) UNIQUE
);

This table has:

• one PRIMARY KEY
• three different uniqueness rules

That is completely normal.

NULL behavior: PRIMARY KEY vs UNIQUE KEY

This is one of the most important practical differences.

PRIMARY KEY

A PRIMARY KEY cannot be NULL.

That is because the row must always be identifiable.

UNIQUE KEY

A UNIQUE KEY enforces uniqueness, but NULL behavior can vary by database engine.

In many databases:

• multiple NULLs are allowed in a UNIQUE column because NULL is treated as an unknown value rather than a regular comparable value

That means a UNIQUE KEY may still allow:

• several rows where the unique column is NULL

Example conceptually:

user_id	email
1	alice@example.com
2	NULL
3	NULL

Depending on the database, that may still satisfy a UNIQUE constraint on email.

This is a major difference from PRIMARY KEY.

Why NULL behavior matters in design

If a field must be:

• unique
• and always present

then you often want:

• NOT NULL
• plus UNIQUE

Example:

email VARCHAR(255) NOT NULL UNIQUE

That gives you both:

• presence
• uniqueness

Without NOT NULL, a UNIQUE KEY may not behave exactly like many people assume, especially around missing values.

That is why strong schema design often combines:

• requiredness
• and uniqueness

explicitly.

PRIMARY KEY usually implies NOT NULL

Because a primary key must identify every row, it is inherently non-NULL.

So this:

user_id BIGINT PRIMARY KEY

already implies the row must have a value for user_id.

That is one reason PRIMARY KEY is stronger than UNIQUE alone as an identity constraint.

UNIQUE KEY does not automatically mean “best identity”

A very common misunderstanding is:

• if a column is unique, it should be the primary key

Not necessarily.

A column can be unique without being the best row identity.

Examples:

• email may change
• phone number may change
• username may change
• external IDs may come from third-party systems
• business codes may change format later

These may all require uniqueness, but that does not mean they are the best core identity for the row.

That is why many schemas keep:

• a stable surrogate PRIMARY KEY
• plus UNIQUE KEY constraints for business fields

This is one of the healthiest database design habits.

Foreign keys and PRIMARY KEY vs UNIQUE KEY

Foreign keys usually reference:

• a PRIMARY KEY or
• a UNIQUE key or unique constraint, depending on the database rules

But in practice, most schema designs point foreign keys at the PRIMARY KEY because:

• it is the official row identity
• it is stable
• and it keeps relationships consistent

Example:

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

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
);

This is much more common than referencing email directly.

That is another practical reason why PRIMARY KEY and UNIQUE KEY serve different roles.

Composite UNIQUE KEY

A UNIQUE KEY can also apply to multiple columns together.

This is very common in multi-tenant or scoped uniqueness models.

Example:

CREATE TABLE members (
    member_id BIGINT PRIMARY KEY,
    organization_id BIGINT NOT NULL,
    email VARCHAR(255) NOT NULL,
    CONSTRAINT uq_members_org_email UNIQUE (organization_id, email)
);

This means:

• the same email cannot appear twice in the same organization
• but the same email could still appear in a different organization if that is allowed by the business rules

This is a very strong example of UNIQUE KEY solving a business rule that PRIMARY KEY is not meant to solve.

Composite PRIMARY KEY vs composite UNIQUE KEY

Both PRIMARY KEY and UNIQUE KEY can be composite. The difference is still their role.

Composite PRIMARY KEY

Defines the official row identity.

Composite UNIQUE KEY

Defines an additional uniqueness rule.

Example:

CREATE TABLE class_enrollment (
    enrollment_id BIGINT PRIMARY KEY,
    student_id BIGINT NOT NULL,
    class_id BIGINT NOT NULL,
    CONSTRAINT uq_student_class UNIQUE (student_id, class_id)
);

This design says:

• enrollment_id is the row identity
• (student_id, class_id) must also be unique so a student cannot enroll twice in the same class

That is a very common and practical pattern.

PRIMARY KEY and indexing

In many relational databases, a PRIMARY KEY is backed by an index.

That helps because primary keys are frequently used for:

• lookups
• joins
• foreign key references
• updates
• deletions

This is one reason primary keys are so central to database performance and identity design.

UNIQUE KEY and indexing

A UNIQUE KEY is also often backed by a unique index or equivalent enforcement mechanism.

That helps the database:

• prevent duplicates
• and often support fast lookups on the constrained column(s)

So both PRIMARY KEY and UNIQUE KEY often influence indexing.

But remember:

• indexing is part of performance
• the real semantic difference is identity versus uniqueness rule

That is still the deeper design distinction.

Common real-world pattern: one PRIMARY KEY, many UNIQUE KEY constraints

A very common table design pattern is:

• one PRIMARY KEY for identity
• several UNIQUE KEY constraints for business rules

Example:

CREATE TABLE employees (
    employee_id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    employee_code VARCHAR(50) NOT NULL UNIQUE,
    payroll_number VARCHAR(50) UNIQUE
);

Here:

• employee_id identifies the row
• email must be unique
• employee_code must be unique
• payroll_number may also need uniqueness

This is exactly the kind of schema where confusing PRIMARY KEY and UNIQUE KEY would lead to worse design.

When PRIMARY KEY and UNIQUE KEY might be the same column

Sometimes a single column can be both:

• the main row identity
• and a unique business value

Example: a small reference table might use a stable code as the PRIMARY KEY.

CREATE TABLE countries (
    country_code CHAR(2) PRIMARY KEY,
    country_name VARCHAR(100) NOT NULL
);

Here:

• country_code is both unique
• and the natural identity of the row

That is perfectly valid.

The key point is not that PRIMARY KEY and UNIQUE KEY must always be different. It is that they answer different design questions.

Sometimes the same column answers both. Often it does not.

When to use PRIMARY KEY

Use a PRIMARY KEY when you need:

• the main identity of the row
• a stable target for foreign keys
• a reliable way to address one row
• one official identifier per table

Most tables should have a PRIMARY KEY.

This is one of the foundational rules of strong relational design.

When to use UNIQUE KEY

Use a UNIQUE KEY when you need to enforce:

• one email per user
• one username per account
• one invoice number per tenant
• one SKU per product catalog
• one employee code per company
• one combination of columns per business rule

UNIQUE KEY is about preventing invalid duplicate values in places where the business logic requires uniqueness.

That is its main job.

Common mistake: using only application checks for uniqueness

A lot of teams write application code like:

• check if the email exists
• if not, insert the new user

That helps, but it is not enough by itself.

Race conditions can still occur. Two requests could pass the check at nearly the same time.

That is why the database should enforce the uniqueness rule too.

Use:

• UNIQUE
• or a UNIQUE KEY constraint

That is much safer.

The application can still validate politely. But the database should enforce the actual rule.

Common mistake: no PRIMARY KEY on important tables

Another common schema problem is forgetting to define a proper PRIMARY KEY.

This causes issues with:

• updates
• deduplication
• foreign keys
• row identity
• ORM behavior
• and maintenance work

In most serious relational designs, an important table should have a clear PRIMARY KEY.

That is one of the most basic database hygiene rules.

Common mistake: making a frequently changing business field the PRIMARY KEY

This often creates pain.

Examples:

• email as primary key
• phone number as primary key
• mutable external code as primary key

These may be unique, but if they can change, they may be a poor choice for the row’s core identity.

That is why many systems prefer:

• stable surrogate PRIMARY KEY
• mutable but unique business columns with UNIQUE KEY constraints

This usually makes relationships much easier to manage.

Common mistake: assuming UNIQUE means NOT NULL

It often does not.

If the business rule is:

• every row must have one email
• and no two emails may match

then you usually want:

email VARCHAR(255) NOT NULL UNIQUE

not just:

email VARCHAR(255) UNIQUE

That is a very important design detail.

A practical decision framework

When deciding between PRIMARY KEY and UNIQUE KEY, ask:

1. Is this the main identity of the row?

If yes, it is likely the PRIMARY KEY.

2. Does this value only need to be unique because of a business rule?

If yes, it is likely a UNIQUE KEY.

3. Can this value change over time?

If yes, be careful about making it the PRIMARY KEY.

4. Do other tables need to reference this row reliably?

If yes, a stable PRIMARY KEY is very valuable.

5. Do I need multiple different uniqueness rules?

If yes, use one PRIMARY KEY plus multiple UNIQUE constraints.

This framework usually leads to much better schema design decisions.

Practical examples

Example 1: users table

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL
);

Meaning:

• user_id identifies the row
• email and username must not repeat

Example 2: tenant-scoped uniqueness

CREATE TABLE members (
    member_id BIGINT PRIMARY KEY,
    organization_id BIGINT NOT NULL,
    email VARCHAR(255) NOT NULL,
    CONSTRAINT uq_members_org_email UNIQUE (organization_id, email)
);

Meaning:

• row identity is member_id
• uniqueness rule is (organization_id, email)

Example 3: natural code as PRIMARY KEY

CREATE TABLE currencies (
    currency_code CHAR(3) PRIMARY KEY,
    currency_name VARCHAR(100) NOT NULL
);

Meaning:

• currency_code is stable enough to serve as identity

This shows that the same concept can take different forms depending on the table.

FAQ

What is the difference between PRIMARY KEY and UNIQUE KEY in SQL?

A PRIMARY KEY is the main row identifier and must be unique and non-NULL, while a UNIQUE KEY also enforces uniqueness but is not necessarily the table’s primary identity column.

Can a table have both a PRIMARY KEY and UNIQUE KEY?

Yes. In fact, that is very common. A table usually has one PRIMARY KEY for row identity and additional UNIQUE KEY constraints for business rules such as unique email addresses or usernames.

Can a table have multiple UNIQUE KEY constraints?

Yes. A table can have multiple UNIQUE KEY constraints on different columns or column combinations, as long as each one enforces a separate uniqueness rule.

Can a PRIMARY KEY contain NULL values?

No. A PRIMARY KEY must always be non-NULL because it is meant to identify each row reliably.

Final thoughts

SQL PRIMARY KEY and UNIQUE KEY both deal with uniqueness, but they solve different design problems.

The clearest way to think about them is:

• PRIMARY KEY = row identity
• UNIQUE KEY = uniqueness rule

That is the core difference.

A strong schema usually uses:

• one PRIMARY KEY to identify the row
• and one or more UNIQUE KEY constraints to enforce business rules

That design gives you:

• cleaner identity
• safer relationships
• clearer database structure
• and stronger integrity

If you keep that distinction clear, you will make much better decisions about table design, foreign keys, and long-term maintainability in relational databases.