SQL Primary Key vs Foreign Key Explained

Understanding Keys in SQL

Relational databases store information in multiple tables. As the database grows, these tables need a reliable way to identify individual records and establish relationships between related data. Primary Keys and Foreign Keys are two of the most important concepts used for this purpose.

A Primary Key uniquely identifies a record inside its own table, while a Foreign Key connects one table with another by referencing a key from the related table. Together, they help maintain data integrity, consistency, and relationships within a relational database.

1. What is a Primary Key?

A Primary Key is a column or combination of columns that uniquely identifies each record in a relational database table. Because every row needs to be distinguishable, the values of the Primary Key must be unique.

Characteristics of a Primary Key

• Uniquely identifies each record.
• Cannot contain duplicate values.
• Cannot normally contain NULL values.
• There is one Primary Key constraint per table.
• The key can contain one column or multiple columns.
• It is commonly used as the target of Foreign Key relationships.

Primary Key SQL Example

CREATE TABLE Customers (

    customer_id INT PRIMARY KEY,

    customer_name VARCHAR(100),

    email VARCHAR(150)

);

Here, customer_id uniquely identifies every customer. Two customers cannot have the same customer_id.

2. What is a Foreign Key?

A Foreign Key is a column or set of columns in one table that references a candidate key, commonly the Primary Key, in another table. It establishes a relationship between records stored in different tables.

For example, an Orders table may contain a customer_id column. That value can reference customer_id in the Customers table. This tells the database which customer owns a particular order.

Foreign Key SQL Example

CREATE TABLE Orders (

    order_id INT PRIMARY KEY,

    order_date DATE,

    customer_id INT,

    FOREIGN KEY (customer_id)

    REFERENCES Customers(customer_id)

);

The customer_id column in Orders references customer_id in Customers. This creates a relationship between the two tables.

How the Two Tables are Connected

Customers.customer_id is the referenced key, while Orders.customer_id stores the relationship.

Primary Key vs Foreign Key: Complete Comparison

Real-World Example

Imagine an online shopping application. The system has thousands of customers and millions of orders. Instead of storing the customer's name and email repeatedly in every order record, the database can store customer information once and use a key to connect orders to that customer.

Because both records contain the value 101, the database knows that Order 5001 belongs to Customer 101.

Inserting Data Using Keys

INSERT INTO Customers
(customer_id, customer_name, email)

VALUES
(101, 'Rahul', 'rahul@example.com');


INSERT INTO Orders
(order_id, order_date, customer_id)

VALUES
(5001, '2026-08-19', 101);

Using Keys with SQL JOIN

Once tables are connected through keys, SQL JOIN operations can combine related information from multiple tables.

SELECT

    c.customer_id,

    c.customer_name,

    o.order_id,

    o.order_date

FROM Customers c

INNER JOIN Orders o

ON c.customer_id = o.customer_id;

The JOIN uses the relationship between Customers.customer_id and Orders.customer_id to retrieve combined information.

Common Mistakes to Avoid

Best Practices for Database Keys

• Choose stable and meaningful key designs.
• Keep Primary Key values unique and non-null.
• Use Foreign Keys to explicitly model relationships.
• Use appropriate constraints to protect data integrity.
• Keep related key columns compatible in definition and data type.
• Consider indexing strategies based on your database workload.
• Use clear and consistent naming conventions.
• Document important table relationships in large databases.

Final Takeaway

Primary Keys and Foreign Keys are fundamental building blocks of relational database design. A Primary Key identifies a record, while a Foreign Key establishes a relationship with another table. Understanding the difference between them is essential for anyone working with SQL, backend development, data analytics, data science, or data engineering.