Primary Key vs Foreign Key in SQL: Explained Simply With Examples

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.

Diagram of a customers table primary key referenced by a foreign key in an orders table

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 NULL isn’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:

TypeWhat it isExampleGood and bad
Natural keyA value that already exists in the real worldEmail address, national ID, ISBNMeaningful, but real-world values change (people change emails)
Surrogate keyA made-up number with no meaning outside the databasecustomer_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_idcustomer_idamount
101125.00
102380.50
103112.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 keyForeign key
JobIdentifies each row in its own tableLinks a row to a row in another table
Unique?Yes, alwaysNo, values can repeat
Can be NULL?NeverYes, unless you add NOT NULL
How many per table?Exactly oneAs many as you need
What it enforcesEntity integrity: every row can be identifiedReferential 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
SyntaxPRIMARY KEY (customer_id)FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
Examplecustomers.customer_idorders.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 keyUnique keyForeign key
Values must be unique?YesYesNo
NULL allowed?NoYes (only one in SQL Server)Yes, unless NOT NULL
How many per table?OneManyMany
Typical useRow ID: customer_idBusiness value: emailLink: 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:

OptionWhat it does
ON DELETE NO ACTION / RESTRICTBlocks the delete if child rows exist (the default in most databases)
ON DELETE CASCADEDeletes the child rows too
ON DELETE SET NULLKeeps 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_lines has a composite primary key (invoice_id, line_no): line 1 of invoice 1001 and line 1 of invoice 1002 are different rows. invoice_id is 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 CASCADE does 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_customers and FK_invoice_lines_products use 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:

DatabaseError
SQL ServerError 547: The DELETE statement conflicted with the REFERENCE constraint “FK_invoices_customers”
MySQL / MariaDBError 1451: Cannot delete or update a parent row: a foreign key constraint fails (… CONSTRAINT `FK_invoices_customers` …)
PostgreSQLupdate 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_id in both tables is much easier to follow than id in one and cust in 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

Scroll to Top