Understanding Primary and Foreign Keys in Entity Relationship Diagrams

Every relational database starts with a blueprint, and that blueprint is the entity relationship diagram. At the center of every ERD sit two concepts that make or break your database design: primary keys and foreign keys. If you get these wrong, your tables won't connect properly, your data will duplicate, and your queries will return garbage. If you get them right, your database stays organized, fast, and reliable. Understanding how primary and foreign keys function inside an ERD is the difference between a database that works and one that creates headaches down the road.

What Is a Primary Key in an Entity Relationship Diagram?

A primary key is a column (or set of columns) that uniquely identifies every single row in a table. Think of it like a Social Security number no two people share the same one, and it always points to exactly one person.

In an ERD, the primary key is typically marked with a key icon or labeled with PK. For example, in a Customer entity, the primary key might be CustomerID. Every customer gets a unique ID, and the database uses that ID to tell customers apart even if two customers share the same name and email address.

A valid primary key has two rules:

It must be unique. No two rows can share the same primary key value.
It cannot be null. Every row must have a value for the primary key you can't leave it blank.

Primary keys form the foundation of data integrity in relational database design. Without them, you have no reliable way to reference a specific record.

What Is a Foreign Key and How Does It Link Tables?

A foreign key is a column in one table that points to the primary key of another table. It's the mechanism that creates relationships between entities in your ERD.

Let's say you have two entities: Customer and Order. The Order table needs to know which customer placed each order. So you add a CustomerID column to the Order table. That CustomerID is a foreign key it references the primary key in the Customer table.

This connection enforces what database designers call referential integrity. It means the database won't allow you to create an order for a customer that doesn't exist. The foreign key acts as a contract between two tables.

Foreign keys also help you understand the cardinality of a relationship whether it's one-to-one, one-to-many, or many-to-many. You can learn more about reading those cardinality symbols and relationship types in an ERD to make your diagrams more precise.

How Do Primary and Foreign Keys Work Together?

The relationship between these two types of keys is what gives a relational database its structure. The primary key identifies. The foreign key connects. Together, they let you split data across multiple tables without losing the links between them.

Here's a simple way to picture it:

The Customer table uses CustomerID (PK) to identify each customer.
The Order table uses OrderID (PK) to identify each order and CustomerID (FK) to link each order back to a customer.
A query can join both tables on CustomerID to show all orders for a specific customer.

This pattern is called normalization splitting data into related tables to reduce redundancy. The primary key / foreign key relationship is the glue that holds normalized tables together. If you want to understand how normalization shapes your ERD structure, take a look at best practices for normalization in ER diagrams.

Can You Show a Practical Example?

Imagine you're designing a database for a small online bookstore. Here's what the key structure might look like:

Entity: Book

BookID (PK) unique identifier for each book
Title
ISBN
Price

Entity: Author

AuthorID (PK) unique identifier for each author
AuthorName
BirthYear

Entity: BookAuthor (junction table for many-to-many)

BookID (FK) references Book.BookID
AuthorID (FK) references Author.AuthorID

In this case, a book can have multiple authors, and an author can write multiple books. The BookAuthor table uses two foreign keys to manage this many-to-many relationship. Neither foreign key alone is unique, but the combination of both serves as a composite primary key.

This is a pattern you'll see in real-world database schemas constantly from hospital systems to e-commerce platforms to HR software.

What Happens When You Get the Keys Wrong?

Mistakes with primary and foreign keys are common, and they create real problems. Here are the ones I see most often:

Using data that can change as a primary key. If you use an email address as a PK and the user changes their email, every foreign key reference breaks. Surrogate keys (auto-incremented IDs) are usually safer.
Allowing duplicate primary keys. This defeats the entire purpose. If two rows share the same PK, the database can't tell them apart.
Missing foreign keys entirely. Some designers create separate tables but forget to define the FK relationships. The data exists, but there's no enforced connection, so referential integrity is lost.
Creating circular references. Table A references Table B, and Table B references Table A. This can cause insertion and deletion problems that are hard to debug.
Not indexing foreign keys. Without indexes, JOIN operations on foreign keys can slow your queries dramatically as your data grows.

Different ERD notations represent keys and relationships in slightly different ways, so the diagram style you choose can also affect how clearly the keys are communicated. A comparison of Crow's Foot and Chen notation styles can help you pick the format that best shows your key relationships.

How Should You Name and Document Your Keys?

Clear naming saves future developers (including your future self) hours of confusion. Here are a few habits that help:

Use consistent naming. If the primary key in the Customer table is CustomerID, name the foreign key CustomerID in every table that references it not CustID, ClientID, or Cust_No.
Mark PKs and FKs explicitly in your ERD. Don't rely on memory. Use labels or symbols so anyone reading the diagram can spot the keys immediately.
Add comments or notes. If a foreign key allows nulls (a so-called "optional relationship"), document why. Not every relationship is mandatory.
Avoid reserved words. Naming a column Key or Index will cause syntax errors in many database systems.

Do Primary Keys Always Have to Be Numbers?

No. Primary keys can be:

Surrogate keys auto-generated integers or UUIDs with no business meaning. These are the most common choice for relational databases.
Natural keys real-world identifiers like ISBN, Social Security Number, or VIN. These carry meaning but risk instability if the data changes.

Most database designers prefer surrogate keys for primary keys because they're stable, compact, and fast to index. Natural keys work when the identifier is genuinely immutable and universally unique but that's rarer than people think.

Quick Checklist: Primary and Foreign Keys in Your ERD

Before you finalize your entity relationship diagram, run through this:

Every entity has a clearly defined primary key marked as PK.
Every relationship between entities uses a foreign key that references the correct primary key.
Foreign key names match their referenced primary key names consistently.
No primary key contains data that might change over time.
Many-to-many relationships are resolved with a junction table containing two foreign keys.
Foreign key columns are indexed for query performance.
Your ERD notation clearly shows which columns are PKs and FKs.

Print this list out the next time you design a schema. It takes five minutes to check, and it prevents the kind of structural problems that cost days to fix after your database is already in production.