# Primary Key vs Foreign Key: What Are the Differences?

> Primary key vs foreign key: what each does and how they differ on uniqueness, NULLs, indexing, and relationships, with SQL examples and a comparison table.

Tianzhou | 2026-07-16 | Source: https://www.bytebase.com/blog/primary-key-vs-foreign-key/

---

| Update History | Comment |
| -------------- | ------- |
| 2025/03/05 | Initial version. |
| 2026/07/16 | Rewrite for 2026: front-load a one-line definition and an at-a-glance comparison table, add the foreign-key indexing gotcha, note current Postgres / MySQL / SQLite behavior, and cut the AI-symmetric misconception and best-practice walls. |

The difference between a primary key and a foreign key comes down to identity versus reference. A **primary key** uniquely identifies each row in a table. A **foreign key** is a column that points at some other table's primary key, which is how you link two tables together.

Think of it like a passport. Your passport number uniquely identifies you, and that is the primary key. When a hotel writes that number on a booking, the booking now refers to you without copying your whole identity, and that is the foreign key. Many bookings can carry the same number, and the number is meaningless unless a real passport with it exists. Referential integrity is just the hotel refusing to file a booking for a passport number that was never issued.

Everything else follows from that one idea. Here is the whole thing at a glance, then the parts that actually matter in practice.

| | Primary key | Foreign key |
| --- | --- | --- |
| What it does | Uniquely identifies each row | Links a row to a row in another table |
| Uniqueness | Values must be unique | Values can, and do, repeat |
| NULL values | Not allowed | Allowed, unless you add `NOT NULL` |
| Per table | At most one | As many as you need |
| Indexing | Created automatically | You usually add it yourself |
| Integrity it enforces | Entity integrity (every row is identifiable) | Referential integrity (no orphaned rows) |

## What a primary key is

A primary key is the column, or set of columns, that uniquely identifies every row. It enforces **entity integrity**, the rule that every row can be told apart from every other. Two things follow. The values are unique, so no two rows share a primary key. And the values are never NULL, because NULL means "unknown" and you cannot identify a row by an unknown value. SQLite is the one that breaks this, letting a NULL sit in a primary key ([a documented quirk](https://sqlite.org/lang_createtable.html), not something to lean on).

Most databases also index the primary key for you, so lookups and joins on it are fast with no extra work.

```sql
CREATE TABLE customers (
    customer_id INT GENERATED ALWAYS AS IDENTITY,  -- MySQL: INT AUTO_INCREMENT
    email VARCHAR(100) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (customer_id)
);
```

Here `customer_id` is the primary key. Two customers can share a name, but they can never share a `customer_id`. A primary key can also span more than one column, which is a **composite key**, and that is the right call when no single column identifies a row on its own.

## What a foreign key is

A foreign key is a column, or set of columns, whose values must match a primary key (or a unique key) in another table. It enforces **referential integrity**, so a reference always points at something real and you never get an order that belongs to a customer who does not exist.

```sql
CREATE TABLE orders (
    order_id INT GENERATED ALWAYS AS IDENTITY,
    customer_id INT NOT NULL,
    order_date TIMESTAMPTZ NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
```

`customer_id` in `orders` is a foreign key pointing at `customer_id` in `customers`. Unlike the primary key, it repeats: one customer places many orders, so the same value shows up on many rows. It can also be NULL when the relationship is optional, unless you mark the column `NOT NULL`.

## The differences that bite

The table up top is the easy part. Here is what actually causes production incidents.

**Foreign keys are usually not indexed for you.** If you take one thing from this post, take this. A primary key gets an index automatically. A foreign key does not, at least in PostgreSQL and SQL Server (MySQL's InnoDB is the exception and creates one). Forget to index a foreign key on a large child table and every `DELETE` or `UPDATE` on the parent has to scan the child to check the constraint. On a 40-million-row `orders` table that turns a one-row delete into a full-table scan. So add the index yourself:

```sql
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
```

**NULL and uniqueness are mirror images.** A primary key is unique and non-NULL by definition. A foreign key is neither: its values repeat, and a NULL is allowed and simply means "not related to anything yet."

**Changing a primary key hurts, changing a foreign key does not.** Once rows reference a primary key you cannot casually change or drop it without touching everything that points at it. A foreign key value you can repoint at a different parent any time, as long as that parent row exists.

**Cascade actions live on the foreign key.** You decide what happens to the child rows when the parent changes:

```sql
CREATE TABLE order_items (
    item_id INT GENERATED ALWAYS AS IDENTITY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (item_id),
    FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (product_id) ON DELETE RESTRICT
);
```

`ON DELETE CASCADE` removes the line items when their order is deleted. `ON DELETE RESTRICT` blocks deleting a product that orders still reference. Pick deliberately, because `CASCADE` is convenient right up until it deletes more than you intended.

## How they fit together

A primary key gives a row a stable identity. A foreign key borrows that identity to express a relationship. They are two halves of the same mechanism, which the e-commerce [schema](/blog/what-is-database-schema/) below shows.

![er](/content/blog/primary-key-vs-foreign-key/er.webp)

- `customers.customer_id` (primary key) is referenced by `orders.customer_id` (foreign key): one customer, many orders.
- `orders.order_id` is referenced by `order_items.order_id`: one order, many items.
- `products.product_id` is referenced by `order_items.product_id`: one product, many line items.
- `product_categories` is a pure join table: its **composite primary key** is made of two foreign keys, `product_id` and `category_id`, which is the standard way to model a many-to-many relationship.

Notice the two ways to model a linking table. `order_items` carries its own surrogate key (`item_id`) plus two foreign keys, because a line item has data of its own (quantity, price) and other tables may want to reference it. `product_categories` carries nothing but the two foreign keys, so the pair itself is the primary key. Both are legitimate; pick by whether the link is a real entity or just a link.

That second pattern trips people up, so it is worth stating plainly: a column can be a foreign key and part of a primary key at the same time. The two are not mutually exclusive. One is about identity, the other about reference.

## A few things people get wrong

1. **Do not use business data as a primary key.** Emails and phone numbers change, and a primary key should not. Use a surrogate. Whether that surrogate is an auto-incrementing integer or a UUID is a real decision, which I went through in [choosing a primary key: UUID or auto-increment](/blog/choose-primary-key-uuid-or-auto-increment/).
2. **Foreign keys are not automatically indexed.** Yes, again. It is the single most common performance surprise in this whole topic.
3. **A foreign key does not have to share the parent's column name.** `orders.buyer_id` can reference `customers.customer_id` fine. The `REFERENCES` clause defines the link, not the name.
4. **Foreign keys do not prevent every orphan on their own.** They block invalid references, but only once you also pick the right `ON DELETE` and `ON UPDATE` behavior.

## Best practices, briefly

1. **Default to a surrogate primary key.** A system-generated `IDENTITY` or `SEQUENCE` value, or a UUID, over a natural business value. Keep it single-column and stable where you can.
2. **Index every foreign key** on a table that takes deletes or updates against its parent. One line, and the scan-the-child problem goes away.
3. **Name your constraints.** `fk_orders_customer` tells you what broke. An auto-generated name does not.
4. **Be explicit about `NOT NULL`.** Mandatory relationship, `NOT NULL`. Genuinely optional, nullable. Do not leave it to accident.
5. **Size the key type for growth.** `INT` tops out near 2.1 billion rows. Reach for `BIGINT` or a UUID before you get close, especially in sharded systems where a single auto-increment counter becomes a bottleneck.

> **Note:** Getting keys and constraints right is a design-review problem, not only a one-time schema choice. [Bytebase](https://www.bytebase.com/)'s SQL review can flag a table created without a primary key, or a foreign key added without a supporting index, before the change ships. It is one of many [database design patterns](/blog/database-design-patterns/) worth enforcing the same way every time.

## Bottom line

A primary key is about identity. A foreign key is about reference. Keep the primary key stable, index the foreign keys, and most of your integrity problems never happen in the first place. After all, a key is only useful if the database can trust it.

## Related reading

- [How to Choose between UUID and Auto Increment Integer / Serial as the Primary Key?](https://www.bytebase.com/blog/choose-primary-key-uuid-or-auto-increment/)
- [Database Design Patterns Every Developer Should Know](https://www.bytebase.com/blog/database-design-patterns/)
- [What is a Database Schema?](https://www.bytebase.com/blog/what-is-database-schema/)