UNIQUE constraint in SQL - Deep Dive

Overview - UNIQUE constraint

What is it?

A UNIQUE constraint in a database ensures that all values in a column or a group of columns are different from each other. It prevents duplicate entries in those columns, helping keep data clean and reliable. This rule applies whenever new data is added or existing data is updated. UNIQUE constraints are a way to enforce data quality automatically.

Why it matters

Without UNIQUE constraints, databases could have repeated or conflicting data, making it hard to trust or use the information. For example, if two users have the same email address in a system that requires unique emails, it could cause confusion or errors. UNIQUE constraints help avoid these problems by stopping duplicates before they happen, saving time and preventing mistakes.

Where it fits

Before learning UNIQUE constraints, you should understand basic database tables and columns. After this, you can learn about PRIMARY KEY constraints, which are a special kind of UNIQUE constraint, and about FOREIGN KEY constraints that link tables together. UNIQUE constraints are part of learning how to keep data accurate and organized.

Mental Model

Core Idea

A UNIQUE constraint is a rule that says no two rows can have the same value in certain columns, ensuring each entry is one of a kind.

Think of it like...

Imagine a guest list for a party where each person must have a unique ticket number. No two guests can have the same ticket number, so the host can easily identify everyone without confusion.

┌───────────────┐
│   Table       │
├───────────────┤
│ ID  │ Email   │
├───────────────┤
│ 1   │ [e-mail] │
│ 2   │ [e-mail] │
│ 3   │ [e-mail] │ <-- Error: duplicate email violates UNIQUE
└───────────────┘

Build-Up - 6 Steps

1

Foundation

What is a UNIQUE constraint

Concept: Introduces the basic idea of UNIQUE constraints in databases.

A UNIQUE constraint is a rule you add to a column or set of columns in a table. It makes sure that every value in those columns is different from all others. For example, if you add a UNIQUE constraint to an email column, no two rows can have the same email address.

Result

The database will reject any new row or update that tries to duplicate a value in the UNIQUE column(s).

Understanding UNIQUE constraints helps you see how databases keep data clean by automatically preventing duplicates.

2

Foundation

How to add UNIQUE constraints

Concept: Shows how to create UNIQUE constraints when making or changing tables.

You can add a UNIQUE constraint when creating a table using SQL like: CREATE TABLE Users ( ID INT, Email VARCHAR(100) UNIQUE ); Or add it later with: ALTER TABLE Users ADD CONSTRAINT unique_email UNIQUE (Email);

Result

The database enforces uniqueness on the Email column from that point forward.

Knowing how to add UNIQUE constraints lets you control data quality from the start or improve it later.

3

Intermediate

UNIQUE constraints on multiple columns

🤔Before reading on: do you think UNIQUE constraints can apply to more than one column at a time? Commit to your answer.

Concept: Explains that UNIQUE constraints can cover combinations of columns, not just one.

A UNIQUE constraint can be set on multiple columns together. This means the combination of values in those columns must be unique, but individual columns can have duplicates. For example: ALTER TABLE Orders ADD CONSTRAINT unique_order UNIQUE (CustomerID, OrderDate); This allows the same CustomerID to appear many times, and the same OrderDate many times, but not the same pair together.

Result

The database rejects rows where the combination of CustomerID and OrderDate already exists.

Understanding multi-column UNIQUE constraints helps you enforce complex uniqueness rules that fit real-world data.

4

Intermediate

Difference between UNIQUE and PRIMARY KEY

🤔Before reading on: do you think UNIQUE and PRIMARY KEY constraints are exactly the same? Commit to your answer.

Concept: Clarifies how UNIQUE constraints differ from PRIMARY KEY constraints.

Both UNIQUE and PRIMARY KEY constraints prevent duplicates. But PRIMARY KEY also means the column(s) cannot be NULL and identifies each row uniquely. UNIQUE columns can have NULLs (depending on the database), and a table can have many UNIQUE constraints but only one PRIMARY KEY.

Result

You can have multiple UNIQUE constraints in a table, but only one PRIMARY KEY, which also acts as the main row identifier.

Knowing this difference helps you design tables with proper keys and uniqueness rules.

5

Advanced

How UNIQUE constraints handle NULL values

🤔Before reading on: do you think UNIQUE constraints treat NULL values as equal or different? Commit to your answer.

Concept: Explores how NULL values interact with UNIQUE constraints, which varies by database.

In many databases, UNIQUE constraints allow multiple NULLs because NULL means 'unknown' and is not considered equal to another NULL. But some databases treat NULLs differently. For example, SQL Server allows only one NULL in a UNIQUE column, while PostgreSQL allows many. This affects how you design your tables.

Result

Depending on the database, you may or may not be able to have multiple rows with NULL in a UNIQUE column.

Understanding NULL behavior prevents unexpected errors or data duplicates in your applications.

6

Expert

Performance and indexing behind UNIQUE constraints

🤔Before reading on: do you think UNIQUE constraints work without any special data structure? Commit to your answer.

Concept: Reveals that UNIQUE constraints rely on special indexes to enforce uniqueness efficiently.

When you add a UNIQUE constraint, the database creates a unique index on the column(s). This index helps quickly check for duplicates when inserting or updating data. Without this index, checking uniqueness would be slow because the database would have to scan all rows. The index also speeds up queries filtering by those columns.

Result

UNIQUE constraints not only enforce rules but also improve query speed on those columns.

Knowing the index behind UNIQUE constraints helps you understand their impact on performance and storage.

Under the Hood

A UNIQUE constraint works by creating a unique index on the specified column(s). When a new row is inserted or an existing row updated, the database uses this index to quickly check if the value or combination of values already exists. If it does, the operation is rejected. This index is a data structure optimized for fast lookups and uniqueness checks.

Why designed this way?

UNIQUE constraints were designed to automate data integrity without manual checks. Using indexes allows the database to enforce uniqueness efficiently even on large tables. Alternatives like scanning all rows would be too slow. The design balances data correctness with performance, making it practical for real-world use.

┌───────────────┐
│   Table Rows  │
├───────────────┤
│ Row 1         │
│ Row 2         │
│ Row 3         │
└─────┬─────────┘
      │
      ▼
┌───────────────┐
│ Unique Index  │
├───────────────┤
│ Value1        │
│ Value2        │
│ Value3        │
└───────────────┘

Insert/Update → Check Unique Index → Accept or Reject

Myth Busters - 4 Common Misconceptions

Quick: Does a UNIQUE constraint always prevent NULL duplicates? Commit to yes or no.

Common Belief: A UNIQUE constraint means no duplicate values, including NULLs.

Reality: Most databases allow multiple NULLs in UNIQUE columns because NULL means unknown, not a value to compare.

Why it matters: Assuming UNIQUE prevents NULL duplicates can cause confusion when multiple NULLs appear, leading to wrong assumptions about data integrity.

Quick: Can a table have multiple PRIMARY KEY constraints? Commit to yes or no.

Common Belief: You can have many PRIMARY KEY constraints on a table.

Reality: A table can have only one PRIMARY KEY, but multiple UNIQUE constraints are allowed.

Why it matters: Trying to add multiple PRIMARY KEYs causes errors and design confusion; understanding this avoids schema mistakes.

Quick: Does a UNIQUE constraint automatically create an index? Commit to yes or no.

Common Belief: UNIQUE constraints are just rules and do not affect indexes.

Reality: UNIQUE constraints create unique indexes to enforce uniqueness efficiently.

Why it matters: Ignoring the index means missing how UNIQUE constraints impact query speed and storage.

Quick: Does a UNIQUE constraint guarantee data is always unique even with concurrent inserts? Commit to yes or no.

Common Belief: UNIQUE constraints always prevent duplicates, no matter what.

Reality: In rare race conditions, without proper transaction isolation, duplicates can occur if the database does not handle concurrency well.

Why it matters: Assuming perfect uniqueness without understanding concurrency can lead to subtle bugs in multi-user systems.

Expert Zone

1

Some databases treat NULLs differently in UNIQUE constraints, affecting how many NULLs are allowed.

2

UNIQUE constraints create unique indexes, which can be used by the query optimizer to speed up searches.

3

Composite UNIQUE constraints enforce uniqueness on combinations, not individual columns, allowing flexible data rules.

When NOT to use

Avoid UNIQUE constraints when you expect duplicates or when NULL handling varies and causes issues. Instead, use application-level checks or triggers for complex rules. For performance-critical systems with heavy writes, consider if the overhead of unique indexes is acceptable.

Production Patterns

In real systems, UNIQUE constraints are used to enforce business rules like unique emails, usernames, or serial numbers. Composite UNIQUE constraints enforce uniqueness on combinations like (user_id, product_id) in orders. They are combined with PRIMARY KEYS and FOREIGN KEYS to maintain data integrity across tables.

Connections

Primary Key

builds-on

Understanding UNIQUE constraints helps grasp PRIMARY KEYS since a PRIMARY KEY is a special UNIQUE constraint that also disallows NULLs and identifies rows.

Indexing

same pattern

UNIQUE constraints rely on unique indexes, so knowing how indexes work deepens understanding of how uniqueness is enforced efficiently.

Set Theory (Mathematics)

conceptual parallel

UNIQUE constraints mirror the mathematical idea of sets where each element is distinct, helping bridge database rules with fundamental math concepts.

Common Pitfalls

#1 Trying to insert duplicate values into a UNIQUE column without handling errors.

Wrong approach:

INSERT INTO Users (Email) VALUES ('[e-mail]');
INSERT INTO Users (Email) VALUES ('[e-mail]');

Correct approach:

INSERT INTO Users (Email) VALUES ('[e-mail]');
-- Handle error or check existence before inserting duplicate

Root cause:

Not understanding that UNIQUE constraints reject duplicates and that inserts must handle these errors.

#2 Assuming UNIQUE constraints prevent multiple NULLs in all databases.

Wrong approach:

CREATE TABLE Items (
Code VARCHAR(10) UNIQUE
);

INSERT INTO Items (Code) VALUES (NULL);
INSERT INTO Items (Code) VALUES (NULL); -- Expect error but allowed in some DBs

Correct approach:

Check your database's NULL handling for UNIQUE constraints and design accordingly, possibly using NOT NULL if duplicates are not allowed.

Root cause:

Misunderstanding how NULL values are treated in UNIQUE constraints across different database systems.

#3 Adding multiple PRIMARY KEY constraints to a table.

Wrong approach:

ALTER TABLE Users ADD PRIMARY KEY (ID);
ALTER TABLE Users ADD PRIMARY KEY (Email);

Correct approach:

Use one PRIMARY KEY and multiple UNIQUE constraints if needed:
ALTER TABLE Users ADD PRIMARY KEY (ID);
ALTER TABLE Users ADD UNIQUE (Email);

Root cause:

Confusing PRIMARY KEY with UNIQUE constraints and not knowing a table can have only one PRIMARY KEY.

Key Takeaways

UNIQUE constraints ensure that values in specified columns are different across all rows, preventing duplicates.

They can apply to single or multiple columns, enforcing uniqueness on combinations of values.

UNIQUE constraints differ from PRIMARY KEYS because they allow NULLs and multiple UNIQUE constraints can exist per table.

Behind the scenes, UNIQUE constraints create unique indexes to efficiently check and enforce uniqueness.

Understanding how NULLs and concurrency affect UNIQUE constraints is essential for designing reliable databases.