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.

Practice

1. Which of the following is TRUE about a PRIMARY KEY in a SQL table?

easy

A. It allows duplicate values but no NULLs.

B. It uniquely identifies each row and cannot contain NULL values.

C. It can contain multiple NULL values.

D. It is used only for indexing and does not enforce uniqueness.

Solution

1. Step 1: Understand the role of PRIMARY KEY

   A PRIMARY KEY uniquely identifies each row in a table and does not allow NULL values to ensure every row is distinct.

2. Step 2: Compare options with PRIMARY KEY properties

   It uniquely identifies each row and cannot contain NULL values. correctly states uniqueness and no NULLs. Other options contradict these rules.

3. Final Answer:

   It uniquely identifies each row and cannot contain NULL values. -> Option B

4. Quick Check:

   PRIMARY KEY = unique + no NULL [OK]

Hint: Primary key = unique + no NULL values [OK]

Common Mistakes:

• Thinking primary keys allow NULLs
• Confusing primary key with unique constraint
• Assuming primary key allows duplicates

2. Which SQL statement correctly defines a column id as a primary key in a table?

easy

A. CREATE TABLE users (id INT, name VARCHAR(50) PRIMARY KEY);

B. CREATE TABLE users (id INT UNIQUE, name VARCHAR(50));

C. CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));

D. CREATE TABLE users (id INT NOT NULL, name VARCHAR(50));

Solution

1. Step 1: Identify correct PRIMARY KEY syntax

   The correct syntax to define a primary key inline is column_name data_type PRIMARY KEY.

2. Step 2: Check each option

   CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); uses correct syntax. CREATE TABLE users (id INT UNIQUE, name VARCHAR(50)); uses UNIQUE, not PRIMARY KEY. CREATE TABLE users (id INT NOT NULL, name VARCHAR(50)); only uses NOT NULL. CREATE TABLE users (id INT, name VARCHAR(50) PRIMARY KEY); incorrectly assigns PRIMARY KEY to the wrong column.

3. Final Answer:

   CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); -> Option C

4. Quick Check:

   PRIMARY KEY syntax = column_name data_type PRIMARY KEY [OK]

Hint: PRIMARY KEY goes right after column type [OK]

Common Mistakes:

• Using UNIQUE instead of PRIMARY KEY
• Placing PRIMARY KEY on wrong column
• Omitting PRIMARY KEY keyword

3. Given the table employees with id as PRIMARY KEY, what happens if you run this SQL?

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

medium

A. The second insert fails due to duplicate primary key.

B. Both rows are inserted successfully.

C. The second insert overwrites the first row.

D. Both inserts fail because primary keys cannot be integers.

Solution

1. Step 1: Understand PRIMARY KEY uniqueness constraint

   PRIMARY KEY enforces unique values. The first insert adds id=1 successfully.

2. Step 2: Analyze second insert with duplicate id

   The second insert tries to add id=1 again, violating uniqueness, causing an error.

3. Final Answer:

   The second insert fails due to duplicate primary key. -> Option A

4. Quick Check:

   Duplicate PRIMARY KEY value causes insert failure [OK]

Hint: Duplicate primary keys cause insert errors [OK]

Common Mistakes:

• Assuming duplicates are allowed in primary key
• Thinking second insert overwrites first
• Believing primary keys cannot be integers

4. You have this table definition:

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

Which problem will occur if you try to insert two rows with product_id as NULL?

medium

A. Both rows insert successfully because UNIQUE allows multiple NULLs.

B. The second insert fails because UNIQUE does not allow duplicate NULLs.

C. Syntax error because NULL is not allowed in UNIQUE columns.

D. Both inserts fail because product_id is not a PRIMARY KEY.

Solution

1. Step 1: Understand UNIQUE constraint with NULLs

   UNIQUE allows only one NULL in some databases, but standard SQL allows multiple NULLs because NULL means unknown.

2. Step 2: Apply to given inserts

   In most SQL systems, inserting multiple rows with NULL in a UNIQUE column succeeds because NULLs are not considered equal.

3. Final Answer:

   Both rows insert successfully because UNIQUE allows multiple NULLs. -> Option A

4. Quick Check:

   UNIQUE allows multiple NULLs in most SQL [OK]

Hint: UNIQUE allows multiple NULLs, PRIMARY KEY does not [OK]

Common Mistakes:

• Assuming UNIQUE disallows multiple NULLs
• Confusing UNIQUE with PRIMARY KEY NULL rules
• Expecting syntax error on NULL in UNIQUE

5. You want to create a table orders where each order has a unique order_id and a unique tracking_number that can be NULL. Which SQL definition is correct?

hard

A. CREATE TABLE orders (order_id INT UNIQUE, tracking_number VARCHAR(20) PRIMARY KEY);

B. CREATE TABLE orders (order_id INT, tracking_number VARCHAR(20) UNIQUE PRIMARY KEY);

C. CREATE TABLE orders (order_id INT PRIMARY KEY UNIQUE, tracking_number VARCHAR(20) NOT NULL UNIQUE);

D. CREATE TABLE orders (order_id INT PRIMARY KEY, tracking_number VARCHAR(20) UNIQUE);

Solution

1. Step 1: Define order_id as PRIMARY KEY

   order_id must be unique and not NULL, so PRIMARY KEY is appropriate.

2. Step 2: Define tracking_number as UNIQUE and nullable

   tracking_number should be unique but can be NULL, so UNIQUE constraint without NOT NULL is correct.

3. Step 3: Evaluate options

   CREATE TABLE orders (order_id INT PRIMARY KEY, tracking_number VARCHAR(20) UNIQUE); matches requirements. CREATE TABLE orders (order_id INT UNIQUE, tracking_number VARCHAR(20) PRIMARY KEY); wrongly assigns PRIMARY KEY to tracking_number. CREATE TABLE orders (order_id INT PRIMARY KEY UNIQUE, tracking_number VARCHAR(20) NOT NULL UNIQUE); makes tracking_number NOT NULL, disallowing NULLs. CREATE TABLE orders (order_id INT, tracking_number VARCHAR(20) UNIQUE PRIMARY KEY); has invalid syntax combining UNIQUE and PRIMARY KEY on tracking_number.

4. Final Answer:

   CREATE TABLE orders (order_id INT PRIMARY KEY, tracking_number VARCHAR(20) UNIQUE); -> Option D

5. Quick Check:

   PRIMARY KEY unique + NOT NULL, UNIQUE allows NULL [OK]

Hint: PRIMARY KEY = unique+not null; UNIQUE allows NULL [OK]

Common Mistakes:

• Making UNIQUE column NOT NULL when NULLs allowed
• Assigning PRIMARY KEY to wrong column
• Combining UNIQUE and PRIMARY KEY on same column incorrectly