Primary keys and uniqueness in SQL - Deep Dive

Overview - Primary keys and uniqueness

What is it?

A primary key is a special column or set of columns in a database table that uniquely identifies each row. It ensures that no two rows have the same value in that column or combination of columns. Uniqueness means that the values in a column or group of columns are all different from each other. This helps keep data organized and easy to find.

Why it matters

Without primary keys and uniqueness, databases would have duplicate or unclear data, making it hard to find or update information correctly. Imagine a phone book where multiple people have the same name and no address to tell them apart. Primary keys solve this by giving each record a unique identity, which is essential for reliable data storage and retrieval.

Where it fits

Before learning about primary keys and uniqueness, you should understand basic database tables and columns. After this, you can learn about foreign keys, which use primary keys to link tables together, and about indexing, which speeds up searching using keys.

Mental Model

Core Idea

A primary key is the unique name tag for each row in a table, ensuring no two rows can be confused or duplicated.

Think of it like...

Think of a primary key like a student ID number at school. Even if two students have the same name, their ID numbers are different, so the school can tell them apart easily.

┌───────────────┐
│   Table: Users│
├───────────────┤
│ ID (PK)       │ ← Unique identifier, no duplicates allowed
│ Name          │
│ Email         │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

What is a Primary Key?

Concept: Introducing the idea of a primary key as a unique identifier for table rows.

In a database table, a primary key is a column or set of columns that uniquely identifies each row. For example, in a table of users, the 'ID' column can be the primary key because each user has a different ID number. This means no two rows can have the same ID.

Result

Each row in the table can be uniquely identified by its primary key value.

Understanding that a primary key prevents duplicate rows is the foundation for reliable data management.

2

Foundation

Uniqueness Constraint Explained

Concept: Understanding uniqueness as a rule that no two rows share the same value in certain columns.

Uniqueness means that the values in a column or group of columns must be different for every row. For example, an email column might have a uniqueness constraint so no two users can register with the same email address.

Result

The database will reject any attempt to insert duplicate values in unique columns.

Knowing uniqueness helps prevent data errors like duplicate accounts or records.

3

Intermediate

Primary Key vs Unique Constraint

🤔Before reading on: do you think a primary key and a unique constraint are exactly the same or different? Commit to your answer.

Concept: Distinguishing between primary keys and unique constraints in a table.

A primary key uniquely identifies each row and cannot be NULL. A unique constraint also enforces uniqueness but can allow NULL values (depending on the database). A table can have only one primary key but multiple unique constraints.

Result

You can have one primary key and several unique columns in the same table.

Understanding the difference clarifies how databases enforce data rules and design tables.

4

Intermediate

Composite Primary Keys

🤔Before reading on: do you think a primary key can be made of more than one column? Commit to your answer.

Concept: Learning that a primary key can consist of multiple columns combined.

Sometimes, a single column is not enough to uniquely identify a row. In that case, a composite primary key uses two or more columns together to ensure uniqueness. For example, in a table recording student course enrollments, the combination of 'student_id' and 'course_id' can be the primary key.

Result

Rows are uniquely identified by the combined values of multiple columns.

Knowing composite keys allows designing tables for complex data relationships.

5

Intermediate

How Databases Enforce Uniqueness

Concept: Explaining the database mechanisms that prevent duplicate primary key or unique values.

When you declare a primary key or unique constraint, the database automatically creates an index to quickly check for duplicates. If you try to insert a row with a duplicate value, the database rejects it and shows an error.

Result

Data integrity is maintained by automatic checks during data insertion or updates.

Understanding enforcement helps explain why some inserts fail and how to fix them.

6

Advanced

Primary Keys and Foreign Keys Relationship

🤔Before reading on: do you think foreign keys must always reference primary keys? Commit to your answer.

Concept: Showing how primary keys link tables through foreign keys.

A foreign key in one table points to a primary key in another table to create a relationship. This ensures that the linked data exists and maintains consistency. For example, an 'orders' table might have a 'customer_id' foreign key referencing the 'customers' table primary key.

Result

Tables are connected, enabling complex queries and data integrity across tables.

Knowing this relationship is key to understanding relational databases and data modeling.

7

Expert

Surprising Effects of NULL in Unique Constraints

🤔Before reading on: do you think NULL values are treated as equal or different in unique constraints? Commit to your answer.

Concept: Exploring how NULL values behave in unique constraints and primary keys.

In many databases, NULL means 'unknown' and is not considered equal to any other value, including another NULL. This means unique constraints can allow multiple NULLs, but primary keys cannot have NULLs at all. This subtlety can cause unexpected duplicates if not understood.

Result

Unique constraints may allow multiple NULLs, but primary keys never allow NULLs.

Understanding NULL behavior prevents subtle bugs in data uniqueness and integrity.

Under the Hood

When a primary key or unique constraint is defined, the database creates an internal index structure, often a B-tree, to store the key values in sorted order. This index allows the database to quickly check for duplicates during inserts or updates by searching the index. The primary key also enforces NOT NULL, meaning every row must have a value. This mechanism ensures fast lookups and maintains data integrity automatically.

Why designed this way?

Primary keys and uniqueness were designed to solve the problem of identifying records uniquely and preventing duplicates. Early databases needed a fast way to find and update records without scanning entire tables. Using indexes for keys was a tradeoff: it uses extra space but greatly improves speed and reliability. Allowing only one primary key per table simplifies relationships and enforces a clear identity for each row.

┌─────────────────────────────┐
│        Table Data           │
│ ┌───────┐ ┌───────────────┐ │
│ │ Row 1 │ │ Primary Key   │ │
│ │ Row 2 │ │ Index (B-tree)│ │
│ │ ...   │ │               │ │
│ └───────┘ └───────────────┘ │
└───────────────┬─────────────┘
                │
                ▼
       ┌─────────────────┐
       │ Duplicate Check │
       │ on Insert/Update│
       └─────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Can a primary key column contain NULL values? Commit to yes or no.

Common Belief: Some people think primary key columns can have NULL values because NULL means unknown and might be allowed.

Reality: Primary key columns cannot contain NULL values; every row must have a valid, unique value.

Why it matters: Allowing NULL in primary keys would break the guarantee of unique identification, causing confusion and errors in data retrieval.

Quick: Does a unique constraint always prevent duplicate NULL values? Commit to yes or no.

Common Belief: Many believe unique constraints treat NULLs as duplicates and prevent multiple NULLs.

Reality: Most databases allow multiple NULLs in unique columns because NULL is not considered equal to any value, including another NULL.

Why it matters: This can lead to unexpected duplicates if NULLs are used as placeholders, causing data integrity issues.

Quick: Can a table have more than one primary key? Commit to yes or no.

Common Belief: Some think tables can have multiple primary keys to cover different unique columns.

Reality: A table can have only one primary key, but it can have multiple unique constraints.

Why it matters: Trying to create multiple primary keys causes errors and confusion in database design.

Quick: Does a composite primary key mean each column is unique by itself? Commit to yes or no.

Common Belief: People often think each column in a composite primary key must be unique alone.

Reality: Only the combination of columns in a composite primary key is unique; individual columns can have duplicates.

Why it matters: Misunderstanding this leads to incorrect assumptions about data uniqueness and faulty queries.

Expert Zone

1

Primary keys are often implemented as clustered indexes, meaning the table data is physically ordered by the primary key, which affects performance and storage.

2

Some databases allow deferred uniqueness checks in transactions, which can cause temporary duplicates until commit time.

3

Choosing natural keys (real-world data) vs surrogate keys (artificial IDs) for primary keys impacts database design and future flexibility.

When NOT to use

Primary keys should not be used when data does not have a natural unique identifier or when the uniqueness rule is complex; in such cases, surrogate keys or UUIDs are better. Also, avoid using large or mutable columns as primary keys because they slow down indexing and updates.

Production Patterns

In production, primary keys are used as the main reference for joins and lookups. Surrogate keys like auto-increment integers or UUIDs are common for simplicity. Composite keys are used in join tables for many-to-many relationships. Unique constraints enforce business rules like unique emails or usernames. Proper indexing on primary keys is critical for performance.

Connections

Foreign Keys

Primary keys are referenced by foreign keys to link tables together.

Understanding primary keys helps grasp how relational databases maintain data consistency across multiple tables.

Hashing in Computer Science

Both primary keys and hashing use unique identifiers to quickly find data.

Knowing how hashing works clarifies why databases use indexes on keys for fast searches.

Unique Identification in Biology

Primary keys are like DNA fingerprints that uniquely identify organisms.

Seeing uniqueness in biology helps appreciate why unique keys are essential for distinguishing data records.

Common Pitfalls

#1 Trying to insert duplicate values into a primary key column.

Wrong approach:

INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (1, 'Bob');

Correct approach:

INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (2, 'Bob');

Root cause:

Misunderstanding that primary keys must be unique and not repeating values.

#2 Allowing NULL values in a primary key column.

Wrong approach:

CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100)
);

INSERT INTO products (product_id, name) VALUES (NULL, 'Widget');

Correct approach:

CREATE TABLE products (
product_id INT PRIMARY KEY NOT NULL,
name VARCHAR(100)
);

INSERT INTO products (product_id, name) VALUES (1, 'Widget');

Root cause:

Not enforcing NOT NULL constraint on primary key columns.

#3 Confusing unique constraints with primary keys and expecting multiple primary keys.

Wrong approach:

ALTER TABLE orders ADD PRIMARY KEY (order_id);
ALTER TABLE orders ADD PRIMARY KEY (customer_id);

Correct approach:

ALTER TABLE orders ADD PRIMARY KEY (order_id);
ALTER TABLE orders ADD UNIQUE (customer_id);

Root cause:

Lack of understanding that a table can have only one primary key but multiple unique constraints.

Key Takeaways

Primary keys uniquely identify each row in a database table and cannot contain NULL values.

Uniqueness constraints ensure no duplicate values in specified columns but can allow NULLs depending on the database.

A table can have only one primary key but multiple unique constraints to enforce different uniqueness rules.

Composite primary keys use multiple columns together to uniquely identify rows when a single column is not enough.

Understanding primary keys is essential for linking tables, maintaining data integrity, and designing efficient databases.