What are primary keys and foreign keys, and why are they important in MySQL?

Updated Feb 20, 2026

Short answer

Keys define relationships and uniqueness in a MySQL database. A primary key uniquely identifies each row, while a foreign key connects rows between tables. They are important because they enforce data integrity and make relational data reliable.

Deep explanation

What is a primary key?

A primary key is a column, or combination of columns, that uniquely identifies every row in a table. MySQL uses it as the official identity of a record.

A primary key has these important properties:

It must contain unique values.
It cannot contain NULL values.
A table can have only one primary key.
It is commonly created using an auto-incrementing integer or a unique identifier.

For example, in a customers table, customer_id can identify each customer:

create-customers.sql

SQL

1CREATE TABLE customers (
2  customer_id INT AUTO_INCREMENT,
3  name VARCHAR(100),
4  email VARCHAR(100),
5  PRIMARY KEY (customer_id)
6);

What is a foreign key?

A foreign key is a column that stores a value referring to a primary key in another table. It creates a relationship between tables and helps MySQL prevent invalid references.

Consider an online store. An orders table needs to know which customer placed each order. Instead of copying customer details into every order row, it stores the customer's primary key as a foreign key.

A primary key answers "Which row is this?" while a foreign key answers "Which other row does this belong to?"

create-orders.sql

SQL

1CREATE TABLE orders (
2  order_id INT AUTO_INCREMENT,
3  customer_id INT,
4  order_date DATE,
5  PRIMARY KEY (order_id),
6  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
7);

How the relationship works

The relationship can be visualized like this:

Rendering diagram…

When a foreign key constraint exists, MySQL can enforce referential integrity. This means:

You cannot insert an order for a customer that does not exist.
You cannot accidentally delete a customer while related orders still depend on it, unless you define appropriate behavior such as CASCADE.

Foreign keys protect the database from relationships that do not make sense.

Why are keys important?

Without keys, relational databases become difficult to trust. Duplicate records, orphaned data, and inconsistent relationships become common problems.

Key type	Purpose	Example
Primary key	Uniquely identifies a row	`customer_id` in `customers`
Foreign key	Links one table to another	`customer_id` in `orders`

Keys also improve query performance. MySQL automatically creates an index for primary keys, and foreign key columns are often indexed to make joins faster.

⚠️ A good database design usually starts with identifying entities and defining how those entities relate before writing application code.

Primary keys and foreign keys are not just syntax features; they represent the rules of the data model. They help developers, applications, and the database engine maintain accurate information as the system grows.

Real-world example

Imagine a food delivery application. It has a users table and an orders table. Each user receives a unique user_id, and every order stores that user_id to show who placed it.

food-delivery-schema.sql

SQL

1CREATE TABLE orders (
2  order_id INT PRIMARY KEY,
3  user_id INT,
4  item VARCHAR(100),
5  FOREIGN KEY (user_id) REFERENCES users(user_id)
6);

A user with user_id = 101 can have many orders, but each order belongs to one user. The foreign key prevents an order from pointing to a user record that does not exist. This keeps the application data consistent even when thousands of users and orders are stored.

Common mistakes

* **Duplicate identifiers** - Using names or emails as primary keys can cause conflicts
use a stable unique identifier instead.
* **Missing constraints** - Storing related IDs without a foreign key allows invalid relationships
define constraints when relationships matter.
* **Confusing keys** - A foreign key does not need unique values because many rows can reference the same parent row.
* **Deleting blindly** - Removing parent records without considering dependencies can create broken data
use rules like `ON DELETE CASCADE` carefully.
* **Overusing joins** - Adding relationships everywhere can complicate queries
design tables around real business relationships.

Follow-up questions

Can a table have multiple foreign keys?
What is the difference between a primary key and a unique key?
What happens if you insert a foreign key value that does not exist?
Should every table have a primary key?