Primary Key vs Foreign Key: What Are the Differences?

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.

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, 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.

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.

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:

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:

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 below shows.

• 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.
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.

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.