Course contentsShow
← Data Science
Lesson 1049 of 2,14527. Database Design and OptimizationFree lesson

Primary Key Constraints and Properties

Understand uniqueness, non-null requirements, and immutability properties that define valid primary keys.

Primary Key Constraints and Properties

What you'll learn: You'll understand the three essential properties that make a column (or combination of columns) a valid primary key in a database table.

The Three Pillars of Primary Keys

Now that you know what primary keys are—unique identifiers for rows in a table—let's explore the strict rules that govern them. A primary key isn't just any column; it must satisfy three fundamental constraints:

1. Uniqueness

Every value in the primary key column must be unique across all rows. No two rows can share the same primary key value. Think of it like fingerprints: each person has a unique one, allowing you to distinguish them from everyone else.

2. Non-Null Requirement

A primary key column cannot contain NULL values. Every row must have a value in the primary key column. This makes sense—if you allow missing identifiers, how would you reliably find that specific row? It's like trying to call someone whose phone number field is blank.

3. Immutability

Once assigned, a primary key value should never change. This property ensures that relationships between tables remain stable over time. Imagine if your Social Security number changed randomly—all records linked to you would break. While databases technically allow updates, changing primary keys is considered a design anti-pattern because foreign keys in other tables reference them.

Why These Properties Matter

These constraints work together to guarantee that each row has a permanent, findable address in your database. Without uniqueness, you'd have duplicates. Without non-null enforcement, some rows would be invisible. Without immutability, your relational links would become unreliable.

Key Takeaway: Valid primary keys must be unique, non-null, and immutable—these three properties ensure every row has a reliable, permanent identifier that other tables can safely reference.