Primary Key vs. Foreign Key: 9 Key Differences With Examples

The main difference between a primary key and a foreign key is what each one does. A primary key uniquely identifies every row in its own table, so its values can't be duplicated or NULL. A foreign key is a column in one table that references the primary key of another table. It links the two tables and ensures every reference points to a row that exists.

Keys are what make a relational database relational. Without them, tables are just disconnected lists. This guide explains what primary and foreign keys are, compares them across nine differences, walks through a real example with SQL, and answers the questions people ask most often about them.

Primary Key 

A primary key is a column, or set of columns, whose values uniquely identify each row in a database table. A table can have only one primary key, and its values must be unique and cannot be NULL.

A primary key is also known as a table's unique identifier or key attribute. When the database generates it automatically, it's called a surrogate key. Think of it as a row's ID card: in a Customers table, customer_id = 101 always refers to exactly one customer.

A primary key does three jobs:

• Enforces entity integrity. No duplicate rows, and no row without an identifier.

• Speeds up lookups. Database systems automatically create a unique index on the primary key.

• Anchors relationships. Other tables reference it through foreign keys.

Primary keys are often auto-generated with AUTO_INCREMENT (MySQL) or IDENTITY (SQL Server, Oracle, PostgreSQL).

CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(50)
);

What is a foreign key?  

A foreign key is a column, or set of columns, in one table whose values must match a primary key or unique key in another table. The table that holds the foreign key is the child table, and the table it references is the parent table.

The role of a foreign key is to create and protect the relationship between the two tables. The database rejects any value that doesn't exist in the parent table. This rule, called referential integrity, prevents orphan records such as an order for a customer who doesn't exist. Read more in our guide to data integrity in a database.

CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
total DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Primary key vs. Foreign key: 9 key differences

Here's how primary keys and foreign keys differ across nine aspects:

Here's a quick way to remember it: a primary key identifies; a foreign key refers.

Can a primary key be a foreign key?

Yes. A primary key can also be a foreign key. The same column uniquely identifies rows in its own table and references the primary key of another table. This is common in two designs: one-to-one relationships and junction tables for many-to-many relationships.

1. One-to-one relationships

Each employee has at most one passport record, so the Passports table can use employee_id as both its primary key and its foreign key:

CREATE TABLE Passports (
employee_id INT PRIMARY KEY,
passport_no VARCHAR(20),
FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);

2. Junction tables (many-to-many)

A student takes many courses, and a course has many students. An Enrollments table holds two foreign keys, and together they form a composite primary key:

CREATE TABLE Enrollments (
student_id INT REFERENCES Students(student_id),
course_id INT REFERENCES Courses(course_id),
PRIMARY KEY (student_id, course_id)
);

The reverse is also true: a foreign key doesn't have to be a primary key. Most foreign keys, like Orders.customer_id, are ordinary columns that can repeat.

Primary key and foreign key example

Consider an online store with two tables. Categories uses category_id as its primary key, for example Q204101 = "Denim." Products has its own primary key, product_id (for example D972 = "Jeans"), plus a category_id foreign key that points to Categories.

Because Products stores only the category's ID and not its name, renaming "Denim" is a single update in Categories. Every product shows the new name the next time the tables are joined. This is normalization: store each fact once, and reference it with keys.

SELECT p.product_name, c.category_name
FROM Products p
JOIN Categories c ON p.category_id = c.category_id;

The foreign key also blocks changes that would break the relationship:

You choose what happens when a parent row changes with referential actions: ON DELETE CASCADE (delete the child rows), SET NULL (keep them but clear the reference) or NO ACTION / RESTRICT (block the change). See the PostgreSQL constraints documentation for the full.

How to know which table needs the foreign key

The foreign key goes in the table on the "many" side of the relationship. One customer has many orders, so customer_id goes in Orders, not the other way around. For one-to-one relationships, put it in the dependent table. For many-to-many relationships, create a junction table that holds both foreign keys.

To decide, ask: "Can one row in table A relate to many rows in table B?" If so, B gets the foreign key. Mapping these relationships up front is the core of good database schema design.

Types of keys in DBMS

Primary and foreign keys belong to a larger family of keys used in relational database management systems (RDBMS):

Super key: any set of columns that uniquely identifies a row. It may include unnecessary extra columns.

Candidate key: a minimal super key, meaning no column can be removed without losing uniqueness.

Primary key: the one candidate key chosen as the table's identifier.

Alternate key: a candidate key that wasn't chosen, such as email when customer_id is the primary key.

Foreign key: a column that references a primary or unique key in another table.

Composite key: two or more columns that are unique only in combination.

Unique key: enforces uniqueness but can accept NULLs. SQL Server allows one NULL, while PostgreSQL, MySQL and Oracle allow many.

Surrogate key: a system-generated ID with no business meaning. Surrogate keys are standard in dimensional modeling.

Primary key vs. candidate key

Every primary key is a candidate key, but not every candidate key is the primary key. A candidate key is any minimal set of columns that could uniquely identify a row. The primary key is the one candidate key you choose, and the rest become alternate keys. For example, both customer_id and email are candidate keys, and customer_id is chosen as the primary key.

Super key vs. primary key

A super key is any combination of columns that uniquely identifies a row, even if some columns are unnecessary. A primary key is a minimal super key with no extra columns. (customer_id, name) is a super key; customer_id alone is the primary key.

Why do relational databases use primary keys and foreign keys?

Relational databases use primary keys to guarantee every row can be uniquely found, and foreign keys to guarantee every relationship between tables is valid. Together, they let you split data into focused tables without duplicating it, join those tables reliably, and keep the data consistent when rows are inserted, updated or deleted.

Keys matter just as much once data leaves the source system. In a data warehouse, fact tables connect to dimension tables through foreign keys. That's the backbone of both designs compared in star schema vs. snowflake schema. When data is integrated from many sources, missing or mismatched keys are one of the most common causes of duplicate records and broken reports. That's why teams validate keys during data model verification and when preparing data for analysis.

Keep Learning

Start with the basics: What is a database? and Types of databases

Go deeper on design: What is a database schema? and Relational database management systems (RDBMS)

Scale it up: Data modeling for a data warehouse and Dimensional modeling guide

Pick your tools: Database management software and AI-powered data modeling

Model Keys and Relationships Without Writing SQL

Defining keys by hand works for a few tables but gets hard to manage across hundreds. Astera Centerprise AI automatically detects primary and foreign keys when you reverse-engineer an existing database. It also lets you define and validate relationships visually, so data stays consistent from source to warehouse.

To learn more about how Astera streamlines the handling of primary and foreign keys, contact us or schedule a demo today.  

Modify Your Database Relationships with Astera

Dive into efficient database management with Astera! Ensure seamless data integrity and relationships using intuitive tools that empower your team to build optimized databases effortlessly. Enhance your database performance today – schedule a demo now!

Request a Demo