If you’ve ever looked at a database diagram and wondered why every table has an id column, and why some tables have extra columns like customer_id sitting in them, this article is for you. Those columns are keys, and they’re what turn a pile of separate tables into a proper relational database.
There are two kinds you need to know as a beginner: primary keys and foreign keys. They sound technical, but the idea behind them is something you already use every day.
Quick answer: what’s the difference?
A primary key uniquely identifies each row in its own table: it can’t repeat and can’t be NULL, and a table has only one. A foreign key is a column that points to the primary key of another table: it can repeat, it can be NULL, and a table can have many. Example: customers.customer_id is a primary key, and orders.customer_id is a foreign key that links each order to its customer.

Table of Contents
An Everyday Way to Think About Keys
Think about your national ID card or your passport number. No two people share the same number, so it identifies you and only you. That’s a primary key.
Now think about a hospital appointment form. It doesn’t copy your whole life story onto the form. It just writes down your ID number, and anyone who needs your details can look you up. That ID number written on someone else’s form is a foreign key.
That’s really all there is to it. The rest is just learning how databases enforce those two ideas.
What Is a Primary Key?
A primary key is a column (or a group of columns) that uniquely identifies each row in a table. It has two strict rules:
- It must be unique. No two rows can have the same value.
- It can’t be empty. Every row must have a value, so
NULLisn’t allowed.
Each table can have only one primary key. Here’s a simple customers table:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150)
);
If you try to insert two customers with customer_id = 1, the database refuses the second one. That’s the point. It protects you from duplicate records that would make your reports wrong.
Natural keys vs. surrogate keys
You’ll hear these two terms a lot:
| Type | What it is | Example | Good and bad |
|---|---|---|---|
| Natural key | A value that already exists in the real world | Email address, national ID, ISBN | Meaningful, but real-world values change (people change emails) |
| Surrogate key | A made-up number with no meaning outside the database | customer_id 1, 2, 3… | Never changes and is fast, but means nothing on its own |
Most developers, me included, use surrogate keys for almost everything. Let the database generate the number for you with AUTO_INCREMENT in MySQL, IDENTITY in SQL Server, or GENERATED ALWAYS AS IDENTITY in PostgreSQL:
-- SQL Server
CREATE TABLE customers (
customer_id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(100) NOT NULL
);
What Is a Foreign Key?
A foreign key is a column in one table that points to the primary key of another table. It’s how tables refer to each other.
Let’s add an orders table. Each order belongs to a customer, so instead of storing the customer’s name and email again in every order, we store their customer_id:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
The last line is the important one. It tells the database: “every value in orders.customer_id must already exist in customers.customer_id.”
| order_id | customer_id | amount |
|---|---|---|
| 101 | 1 | 25.00 |
| 102 | 3 | 80.50 |
| 103 | 1 | 12.75 |
Notice that customer 1 appears twice. That’s fine. Unlike a primary key, a foreign key can repeat, because one customer can place many orders.
Primary Key vs. Foreign Key: Side by Side
| Primary key | Foreign key | |
|---|---|---|
| Job | Identifies each row in its own table | Links a row to a row in another table |
| Unique? | Yes, always | No, values can repeat |
| Can be NULL? | Never | Yes, unless you add NOT NULL |
| How many per table? | Exactly one | As many as you need |
| What it enforces | Entity integrity: every row can be identified | Referential integrity: every link points to a real row |
| Index created automatically? | Yes (a clustered index by default in SQL Server) | Only in MySQL (InnoDB). Not in SQL Server or PostgreSQL |
| Syntax | PRIMARY KEY (customer_id) | FOREIGN KEY (customer_id) REFERENCES customers (customer_id) |
| Example | customers.customer_id | orders.customer_id |
Primary Key vs Foreign Key vs Unique Key
A third key often joins the comparison. A unique key (a UNIQUE constraint) stops duplicates like a primary key does, but it’s meant for business values such as an email or an employee code rather than the row’s main ID:
| Primary key | Unique key | Foreign key | |
|---|---|---|---|
| Values must be unique? | Yes | Yes | No |
| NULL allowed? | No | Yes (only one in SQL Server) | Yes, unless NOT NULL |
| How many per table? | One | Many | Many |
| Typical use | Row ID: customer_id | Business value: email | Link: orders.customer_id |
A foreign key can point at a unique key as well as a primary key, because both guarantee there’s exactly one matching row. For more on unique keys, see SQL Constraints Explained Simply.
Why Bother? What Keys Protect You From
You could build tables without any keys and the database would happily store your data. The trouble shows up later. Keys give you something called referential integrity, which is a fancy way of saying “the links between tables always make sense”. In practice that means:
- You can’t create an order for customer 99 if customer 99 doesn’t exist.
- You can’t delete a customer who still has orders, unless you decide what should happen to those orders.
- You never end up with “orphan” rows that point to nothing.
I’ve worked on older systems where the foreign keys were never created “to keep things simple”. Years later, the tables were full of orders pointing at deleted customers, and every report needed extra filters to hide the mess. Adding keys from day one is much cheaper than cleaning up afterwards.
What happens when you delete a parent row?
You get to choose. These options go on the foreign key:
| Option | What it does |
|---|---|
ON DELETE NO ACTION / RESTRICT | Blocks the delete if child rows exist (the default in most databases) |
ON DELETE CASCADE | Deletes the child rows too |
ON DELETE SET NULL | Keeps the child rows but clears the foreign key value |
Be careful with CASCADE. It’s convenient, but one delete can quietly remove hundreds of related rows.
How Keys Make JOINs Work
Keys and JOINs go hand in hand. When you want to see each order with the customer’s name, you join on the key columns:
SELECT o.order_id, c.name, o.amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;
The primary key on one side and the foreign key on the other are what make this join reliable. If you want a refresher on how joins work, see SQL JOINs Explained Simply.
Composite Keys (A Quick Mention)
Sometimes no single column is unique on its own, but a combination is. Picture a table that records which students are enrolled in which courses. A student appears many times and so does a course, but the pair (student, course) should only appear once:
CREATE TABLE enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id)
);
That’s a composite primary key. Both columns are usually also foreign keys pointing at students and courses.
Primary and Foreign Key Examples from a Real Business Database
The customers and orders example shows the idea. Real systems such as sales, accounting and inventory use the same two keys in a few patterns that come up again and again. Here are the ones worth knowing.
Example 1: invoices and invoice lines
An invoice has a header (who, when) and lines (what, how many, at what price). Each line points to its invoice and to a product:
CREATE TABLE invoices (
invoice_id INT PRIMARY KEY,
customer_id INT NOT NULL,
invoice_date DATE NOT NULL,
CONSTRAINT FK_invoices_customers
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
CREATE TABLE invoice_lines (
invoice_id INT NOT NULL,
line_no INT NOT NULL,
product_id INT NOT NULL,
quantity DECIMAL(10,2) NOT NULL,
unit_price DECIMAL(12,3) NOT NULL,
CONSTRAINT PK_invoice_lines PRIMARY KEY (invoice_id, line_no),
CONSTRAINT FK_invoice_lines_invoices
FOREIGN KEY (invoice_id) REFERENCES invoices (invoice_id)
ON DELETE CASCADE,
CONSTRAINT FK_invoice_lines_products
FOREIGN KEY (product_id) REFERENCES products (product_id)
);
This small design shows three rules at once:
invoice_lineshas a composite primary key(invoice_id, line_no): line 1 of invoice 1001 and line 1 of invoice 1002 are different rows.invoice_idis part of the primary key and a foreign key at the same time.- CASCADE where the child can’t exist alone. Lines mean nothing without their invoice, so deleting a draft invoice should delete its lines.
ON DELETE CASCADEdoes that. - No CASCADE where the parent is shared. A customer or a product is used by many invoices. Deleting customer 1 must be blocked while invoices exist, never cascade into deleting their invoices. That’s why
FK_invoices_customersandFK_invoice_lines_productsuse the default (no action).
In accounting systems, posted invoices are usually never deleted at all: they’re cancelled with a status or a credit note. The foreign keys then act as a safety net that stops anyone from deleting a customer or product that has history.
Example 2: a foreign key that points to its own table
A chart of accounts is a tree: “Cash” and “Bank” belong to “Current Assets”, which belongs to “Assets”. Each account stores its parent’s ID, and the foreign key points back to the same table:
CREATE TABLE accounts (
account_id INT PRIMARY KEY,
account_name VARCHAR(100) NOT NULL,
parent_account_id INT NULL,
CONSTRAINT FK_accounts_parent
FOREIGN KEY (parent_account_id) REFERENCES accounts (account_id)
);
INSERT INTO accounts VALUES
(1000, 'Assets', NULL),
(1100, 'Current Assets', 1000),
(1110, 'Cash', 1100),
(1120, 'Bank', 1100);
SELECT child.account_name, parent.account_name AS parent_name
FROM accounts child
LEFT JOIN accounts parent ON parent.account_id = child.parent_account_id;
The top-level account has NULL as its parent, which is why this foreign key column allows NULL. The same pattern is used for employees and their managers, product categories and sub-categories, and branches and regions. Joining a table to itself like this is covered in SQL Self Join Explained Simply.
Give your keys names, and you get readable errors
If you don’t name a constraint, the database invents a name like FK__invoices__custo__3B75D760. Name it yourself (FK_child_parent is a common convention) and the error message tells you exactly what went wrong. Here is what happens when someone tries to delete a customer who still has invoices:
| Database | Error |
|---|---|
| SQL Server | Error 547: The DELETE statement conflicted with the REFERENCE constraint “FK_invoices_customers” |
| MySQL / MariaDB | Error 1451: Cannot delete or update a parent row: a foreign key constraint fails (… CONSTRAINT `FK_invoices_customers` …) |
| PostgreSQL | update or delete on table “customers” violates foreign key constraint “fk_invoices_customers” |
Inserting an invoice for a customer that doesn’t exist gives a similar error (547 in SQL Server, 1452 in MySQL). In the application, catch these errors and show a friendly message instead, for example “This customer has invoices and can’t be deleted.” The constraint name tells you which message to show; SQL Constraints Explained Simply shows how to map names to messages.
Adding a foreign key to an old table
Older databases often have tables that were built without foreign keys. Before you can add one, you have to find the “orphan” rows that point to a customer that no longer exists, otherwise ALTER TABLE fails:
-- 1. Find orders whose customer doesn't exist
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE o.customer_id IS NOT NULL
AND c.customer_id IS NULL;
-- 2. Fix them: restore the customer, re-assign the orders, or set to NULL
-- 3. Then add the key
ALTER TABLE orders
ADD CONSTRAINT FK_orders_customers
FOREIGN KEY (customer_id) REFERENCES customers (customer_id);
In SQL Server you may see advice to add the key WITH NOCHECK to skip the existing rows. That works, but the key is then marked “not trusted”, and SQL Server can’t use it to simplify queries. Clean the data and add the key normally whenever you can.
Adding or removing keys on an existing table
You can add a primary key to a table that already has data, as long as the column has no duplicates and no NULL values. Make it NOT NULL first if it isn’t already:
-- Add a primary key (all three databases)
ALTER TABLE customers
ADD CONSTRAINT PK_customers PRIMARY KEY (customer_id);
-- Remove keys: SQL Server and PostgreSQL
ALTER TABLE orders DROP CONSTRAINT FK_orders_customers;
ALTER TABLE customers DROP CONSTRAINT PK_customers;
-- Remove keys: MySQL
ALTER TABLE orders DROP FOREIGN KEY FK_orders_customers;
ALTER TABLE customers DROP PRIMARY KEY;
Drop the foreign keys that point at a primary key before you drop the primary key itself, otherwise the database refuses. This is another place where clear constraint names save time.
Find foreign keys without an index (SQL Server)
SQL Server doesn’t index foreign key columns for you. In the example above, invoice_lines.product_id has no index, so “show all sales of this product” and every delete check on products has to scan the whole lines table. This query lists foreign keys whose first column isn’t the first column of any index:
SELECT fk.name AS foreign_key,
OBJECT_NAME(fk.parent_object_id) AS table_name,
c.name AS column_name
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc
ON fkc.constraint_object_id = fk.object_id
AND fkc.constraint_column_id = 1
JOIN sys.columns AS c
ON c.object_id = fkc.parent_object_id
AND c.column_id = fkc.parent_column_id
WHERE NOT EXISTS (
SELECT 1
FROM sys.index_columns AS ic
WHERE ic.object_id = fkc.parent_object_id
AND ic.column_id = fkc.parent_column_id
AND ic.key_ordinal = 1
)
ORDER BY table_name;
For each row, consider an index such as CREATE INDEX IX_invoice_lines_product_id ON invoice_lines (product_id); on tables that are large or often joined. (invoice_lines.invoice_id doesn’t need one, because it’s already the first column of the primary key.) MySQL’s InnoDB creates foreign key indexes automatically.
Beginner Tips
- Give every table a primary key. No exceptions.
- Name keys clearly:
customer_idin both tables is much easier to follow thanidin one andcustin the other. - Make sure the foreign key column has the same data type as the primary key it points to.
- Add an index on foreign key columns. Some databases do this for you and some (like SQL Server and PostgreSQL) don’t, and joins get slow without it.
Frequently Asked Questions
Can a primary key also be a foreign key?
Yes. It happens in two common designs. In a one-to-one table, the child uses the parent’s ID as its own primary key, so each customer can have at most one details row:
CREATE TABLE customer_details (
customer_id INT PRIMARY KEY,
notes VARCHAR(500),
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
And in a link table like enrollments above, each column of the composite primary key is also a foreign key to its own table.
Does a foreign key have to be a primary key?
No. The foreign key column itself is usually an ordinary column, and it doesn’t have to reference a primary key either. It must point to a column (or set of columns) that is guaranteed to be unique: usually the primary key, but a column with a UNIQUE constraint works too. For example, an orders table could reference customers.email if email is declared UNIQUE. In practice, pointing at the primary key is simpler and is what you should do by default.
Can a foreign key be NULL?
Yes, unless you declare the column NOT NULL. A NULL foreign key means “not linked to anything yet”, such as an order with no assigned salesperson. If every row must have a parent, add NOT NULL. More on this in NULL in SQL Explained Simply.
Can a table have more than one primary key or foreign key?
A table has only one primary key, although it can be made of several columns (a composite key). It can have many foreign keys: an orders table might point to customers, employees and branches at the same time.
Do the two columns need the same name?
No. Only the data type has to match (for example INT to INT). Using the same name in both tables just makes the design easier to read.
Conclusion
A primary key answers “which row is this?” and a foreign key answers “which row does this belong to?”. Together they keep your data consistent and make it possible to connect tables with confidence.
Next time you design a table, start by deciding its primary key, then ask which other tables it needs to point to. If you’re still getting comfortable with querying, our guide to WHERE, ORDER BY and LIMIT is a good next read.
Related articles
- SQL Constraints Explained Simply: NOT NULL, UNIQUE, CHECK and DEFAULT.
- Database Normalization: 1NF, 2NF and 3NF: remove duplicate data step by step.
- SQL Indexes Explained Simply: what indexes are and when to add one.
- SQL Data Types Explained Simply: choosing the right type for each column.