Primary Key vs Foreign Key Explained with Examples

Primary key vs foreign key is one of the most important concepts in relational databases. If this is unclear, table design, joins, and data integrity rules become confusing very quickly.

In this guide, you will learn the exact difference with practical SQL examples and production-friendly design tips. If you are building your foundation, pair this with Relational Databases Explained: Tables, Rows, and Keys and SQL vs NoSQL: Complete Guide to Differences, Use Cases, and Examples. For the full learning path, use the Database Fundamentals series as the pillar page and the Database tag as the category page.

Table of Contents

What Is a Primary Key?

A primary key uniquely identifies each row in the same table.

For example, in a customers table, customer_id is usually the primary key.

Primary Key Rules

• Must be unique
• Cannot be NULL
• Usually should not change after creation
• Can be a single column or multiple columns (composite primary key)

CREATE TABLE customers (
  customer_id BIGINT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Think of primary keys as each row’s permanent identity inside the table.

What Is a Foreign Key?

A foreign key is a column (or group of columns) in one table that references a primary key (or unique key) in another table.

It creates and enforces the relationship between tables.

CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  order_total DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Here, orders.customer_id is a foreign key pointing to customers.customer_id.

That means you cannot insert an order for a customer that does not exist.

Primary Key vs Foreign Key: Core Differences

A useful shortcut:

• Primary key answers: “Who is this row?”
• Foreign key answers: “Which parent row does this row belong to?”

SQL Example: Customers and Orders

Let us walk through a practical mini schema.

CREATE TABLE customers (
  customer_id BIGINT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  status VARCHAR(30) NOT NULL,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Valid Inserts

INSERT INTO customers (customer_id, full_name)
VALUES (101, 'Ava Patel');

INSERT INTO orders (order_id, customer_id, status)
VALUES (5001, 101, 'PLACED');

Invalid Insert (Rejected by Database)

-- Fails because customer_id 999 does not exist in customers
INSERT INTO orders (order_id, customer_id, status)
VALUES (5002, 999, 'PLACED');

The database rejects this row and protects data consistency automatically.

Cascade Rules: ON DELETE and ON UPDATE

Foreign keys can include actions for parent updates/deletes.

CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

Common Options

• RESTRICT or NO ACTION: block parent delete/update when child rows exist
• CASCADE: propagate change/delete to child rows
• SET NULL: set child foreign key to NULL after parent delete
• SET DEFAULT: set child foreign key to default value

In most business systems, ON DELETE RESTRICT is safer by default for critical data like payments and invoices.

Common Design Mistakes and Fixes

1. Using Mutable Business Data as Primary Key

Bad pattern: using email as a primary key.

• Problem: emails can change
• Fix: use stable surrogate IDs (BIGINT, UUID) as primary key and keep email as UNIQUE

2. Missing Foreign Key Constraints

Bad pattern: relationships only in application code.

• Problem: inconsistent or orphaned rows appear over time
• Fix: enforce foreign key constraints in the database, not only in the API layer

3. Overusing Cascading Deletes

Bad pattern: ON DELETE CASCADE everywhere.

• Problem: one delete can wipe large dependent datasets
• Fix: use cascades intentionally and prefer soft-delete for sensitive domains

4. Ignoring Indexes on Foreign Keys

Bad pattern: foreign key column without index.

• Problem: slow joins and delete/update checks on parent tables
• Fix: add indexes for frequently joined foreign key columns

If your dataset grows, key design and indexing choices directly impact scale patterns such as database sharding.

When to Use Composite Keys

A composite key uses multiple columns together.

Example: an order_items table where one order can have many products.

CREATE TABLE order_items (
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

Use composite keys when the combination itself is the natural identity.

For many teams, a hybrid model also works:

• Surrogate primary key (id) for simplicity
• Composite UNIQUE constraint for business-level uniqueness

Real-World Examples

E-commerce (Amazon-like)

• customers.customer_id is the primary key in customers
• orders.customer_id is a foreign key to customers
• order_items.order_id is a foreign key to orders

This design prevents impossible states such as order items without an order.

Banking Systems

• accounts.account_id is a primary key
• transactions.account_id is a foreign key

Banks rely on foreign key checks and transactions together to maintain ledger consistency.

SaaS Multi-Tenant Apps

• tenants.tenant_id is a primary key
• users.tenant_id, projects.tenant_id, and invoices.tenant_id are foreign keys

This keeps tenant boundaries explicit and queryable at every layer.

Interview Questions

1. What is the difference between primary key and foreign key?

A primary key uniquely identifies each row in its own table and cannot be NULL. A foreign key references a key in another table to define relationships and enforce referential integrity. Primary keys are about identity; foreign keys are about association.

2. Can a table have multiple foreign keys?

Yes. A table can have many foreign keys, each pointing to different parent tables (or even the same table in self-referencing designs). Example: orders might have customer_id, shipping_address_id, and billing_address_id as separate foreign keys.

3. Can a foreign key be null?

Yes, unless you explicitly mark the column NOT NULL. Nullable foreign keys are useful for optional relationships, such as employees.manager_id where top-level executives may not have a manager.

4. Why should we index foreign key columns?

Parent row updates/deletes often trigger foreign key checks, and joins commonly use foreign key columns. Without indexes, these operations may require full table scans and become expensive as data grows.

5. When would you use a composite primary key?

Use composite keys when multiple columns together form the natural unique identity, such as (order_id, product_id) in order_items. This prevents duplicate line items and models domain rules directly in schema design.

6. Should we always use cascading deletes?

No. Cascading deletes are powerful but risky for high-value data. Use them when child rows are strictly dependent and safe to remove automatically. For billing, audit, or compliance-heavy domains, RESTRICT or soft-delete is usually safer.

Conclusion

Understanding primary key vs foreign key is a core milestone in database design. Primary keys define row identity, foreign keys define table relationships, and together they protect your system from invalid states.

As you design schemas, prioritize stable primary keys, enforce foreign key constraints in the database, and choose cascade behavior deliberately. These habits prevent data-quality problems long before they reach production.

References

1. PostgreSQL Documentation: Constraints
2. MySQL Documentation: FOREIGN KEY Constraints
3. SQLite Documentation: Foreign Key Support

YouTube Videos

1. “SQL Primary and Foreign Keys (explained simply)” - Teddy Smith [https://www.youtube.com/watch?v=2SYyb6gNpfg]

2. “Primary Key and Foreign Key Constraints in SQL” - Neso Academy [https://www.youtube.com/watch?v=MR0PK0qkCD8]

3. “Primary Key and Foreign Key Tutorial in MySQL” - edureka! [https://www.youtube.com/watch?v=unREmbNASaI]