Understanding Primary Keys and Foreign Keys in SQL

When you're learning SQL and relational databases, two terms come up early and often: primary keys and foreign keys. These concepts are central to how relational databases structure and connect data.

In this guide, we’ll explain what primary and foreign keys are, why they matter, how to use them properly, and some common mistakes to avoid. Whether you're designing your first database schema or debugging a JOIN that doesn’t work, this article will give you a foundational understanding of how keys maintain integrity and establish relationships between tables.

What Is a Primary Key?

A primary key is a column or set of columns that uniquely identifies each row in a table. Every table in a well-designed database should have a primary key.

Characteristics of a Primary Key:

• Unique: No two rows can have the same primary key value.
• Not Null: A primary key column cannot contain NULL values.
• Stable: The value should rarely (if ever) change.
• Minimal: It should only contain the necessary columns needed to ensure uniqueness.

Example:

Imagine a users table:

CREATE TABLE users (
 id SERIAL PRIMARY KEY,
 name VARCHAR(100),
 email VARCHAR(255)
);


Here, id is the primary key. Every user has a unique id, and this value identifies each record in the table.

What Is a Foreign Key?

A foreign key is a column (or set of columns) in one table that references the primary key in another table. Foreign keys create relationships between tables and help enforce referential integrity, which means the database ensures that relationships between tables remain valid.

Characteristics of a Foreign Key:

• Points to a primary key in another table (or the same table in self-referencing relationships).
• Can have duplicate values.
• Can be NULL (if the relationship is optional).
• Helps prevent orphan records.

Example:

Now, let’s say we have an orders table that stores orders made by users:

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 user_id INTEGER REFERENCES users(id),
 order_date DATE,
 total_amount DECIMAL(10, 2)
);


Here, user_id is a foreign key that references the id column in the users table. This ensures that every order is tied to a valid user.

Why Are Primary and Foreign Keys Important?

Understanding and using keys properly is crucial for:

1. Data Integrity

Foreign keys prevent situations where a record refers to a non-existent row. For example, you can’t insert an order for a user_id that doesn’t exist.

2. Database Normalization

Keys allow you to split data into multiple related tables, reducing redundancy and improving maintainability.

3. Efficient Joins

Relational databases are optimized to JOIN tables based on key relationships, which improves performance and clarity.

4. Cascading Behavior

Foreign keys can define what happens when a related record is updated or deleted. For example:

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE


This will automatically delete orders when the related user is deleted.

Syntax for Defining Keys in SQL

There are several ways to define primary and foreign keys in SQL.

Defining Primary Keys:

-- Inline
id SERIAL PRIMARY KEY

-- Table-level
CREATE TABLE products (
 product_id INT,
 name VARCHAR(100),
 PRIMARY KEY (product_id)
);


Defining Foreign Keys:

-- Inline
user_id INTEGER REFERENCES users(id)

-- Table-level
CREATE TABLE posts (
 post_id INT PRIMARY KEY,
 author_id INT,
 FOREIGN KEY (author_id) REFERENCES users(id)
);


Composite Keys

Sometimes a single column isn't enough to uniquely identify a row. In that case, you can use composite keys, which are a combination of two or more columns.

Example:

CREATE TABLE enrollments (
 student_id INT,
 course_id INT,
 enrollment_date DATE,
 PRIMARY KEY (student_id, course_id)
);


In this case, the combination of student_id and course_id ensures uniqueness.

Foreign keys can also reference composite primary keys:

FOREIGN KEY (student_id, course_id) REFERENCES enrollments(student_id, course_id)


Real-World Use Cases

E-commerce

• users(id) → primary key
• orders(user_id) → foreign key
• order_items(order_id) → foreign key

This structure ensures that every order belongs to a user, and every item belongs to a valid order.

Blog Platform

• authors(author_id) → primary key
• posts(author_id) → foreign key
• comments(post_id) → foreign key

This allows queries like:

SELECT * FROM posts WHERE author_id = 5;


Or to join posts with authors:

SELECT p.title, a.name FROM posts p JOIN authors a ON p.author_id = a.author_id;


Common Mistakes and How to Avoid Them

❌ Not Defining Primary Keys

Every table should have a primary key. Without one, your data may be inconsistent, and you’ll run into trouble with JOINs.

❌ Duplicate or NULL Primary Keys

If your primary key isn’t truly unique or allows NULLs, the table structure is flawed.

❌ Forgetting to Index Foreign Keys

While most databases index primary keys by default, foreign keys are not always indexed. Indexing them improves JOIN performance.

❌ Using Arbitrary Keys Instead of Natural Keys

Avoid using arbitrary combinations like first_name + last_name as a primary key. Use a synthetic key (like id) for simplicity.

Best Practices

• Use SERIAL or AUTO_INCREMENT (MySQL) for primary key columns.
• Always explicitly define your foreign key constraints.
• Use cascading actions thoughtfully — they can be powerful but also destructive.
• Use singular table names (user, order) or plural (users, orders) consistently.
• Stick with integer-based keys for performance and simplicity.

Final Thoughts

Primary and foreign keys are the backbone of relational databases. They ensure that your data is reliable, consistent, and connected in meaningful ways. Understanding how to use keys effectively is essential for anyone working with SQL.

By applying the principles outlined here, you’ll be able to build schemas that scale, support efficient queries, and prevent data anomalies.

Want to try building key relationships in a real SQL workspace? Get started with Galaxy — an AI-powered SQL editor designed for fast querying and schema exploration.

Continue learning: