Primary Key and Foreign Key - The Relationship System Inside Databases
The Hidden Structure Behind Every Database Application
Every serious web application depends on relationships between data. A user places an order. An order contains products. A student joins a course. A blog post belongs to an author. A payment belongs to an invoice. A comment belongs to a post. A delivery address belongs to a customer.
These relationships may look simple from the outside, but inside the database they need a proper structure. Without structure, data becomes messy very quickly.
This is where primary keys and foreign keys become important.
A primary key identifies one record in a table. A foreign key connects one table to another table. Together, they form the relationship system inside relational databases.
Many beginners learn primary key and foreign key only as definitions. They understand that a primary key is unique and a foreign key refers to another table. But in real projects, these keys do much more. They protect data, prevent orphan records, improve database design, support joins, help reporting, and make full stack applications easier to maintain.
A database without proper keys is like a city without house numbers and road connections. Data may exist, but finding, connecting, and protecting it becomes difficult.
Primary Key Meaning in Simple Words
A primary key is a column or group of columns that uniquely identifies each row in a table.
For example, in a users table, every user needs a unique identity. Two users may have the same name. Some users may even share similar email formats. But the database needs one reliable value that identifies each user exactly.
A simple users table can use id as the primary key.
CREATE TABLE users (
id BIGINT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Here, id is the primary key. It identifies each user row.
The primary key should not be empty. It should not be duplicated. It should remain stable.
If the application wants to update one user, fetch one user, delete one user, or connect that user to another table, the primary key makes it possible.
Why a Primary Key Is Needed
A table without a primary key may still store data, but it becomes risky.
Imagine a users table with only name and email.
CREATE TABLE users_without_key (
name VARCHAR(100),
email VARCHAR(150)
);
This table can store data, but it has no guaranteed identity column. If duplicate records appear, updating or deleting the correct row becomes difficult.
Example problem:
Arun Kumar | arun@example.com
Arun Kumar | arun@example.com
Which row is the real one? Which row should be connected to orders? Which row should be updated?
A primary key solves this problem by giving every row a stable identity.
In real applications, primary keys are used for API endpoints, joins, relationships, logs, admin panels, user actions, and backend processing.
Example API:
GET /users/25
Here, 25 is usually the primary key of the user.
Without primary keys, backend development becomes unreliable.
Foreign Key Meaning in Simple Words
A foreign key is a column in one table that refers to the primary key of another table.
For example, an order belongs to a user. The orders table can store user_id as a foreign key.
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
order_status VARCHAR(30) NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
);
Here, orders.user_id is a foreign key. It refers to users.id.
This means an order cannot belong to a user who does not exist.
That protection is called referential integrity.
Foreign keys are important because they make relationships real at the database level. The application code may also check relationships, but the database itself becomes the final safety layer.
Primary Key and Foreign Key Working Together
Primary keys and foreign keys work together to connect tables.
A primary key says:
This row has a unique identity.
A foreign key says:
This row is connected to another row in another table.
Example relationship:
users.id ---> orders.user_id
The users table owns the main identity. The orders table stores a reference to that identity.
This is how relational databases build relationships without copying all user details into every order.
A user row may contain user information. The order row only needs the user’s ID.
This avoids repeated data and makes the database easier to maintain.
Real Example: User and Orders
Consider a simple e-commerce application.
A user can place many orders. This is a one-to-many relationship.
One user can have many orders.
One order belongs to one user.
SQL structure:
CREATE TABLE users (
id BIGINT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
order_status VARCHAR(30) NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
Insert a user:
INSERT INTO users (id, name, email)
VALUES (1, 'Arun Kumar', 'arun@example.com');
Insert an order for that user:
INSERT INTO orders (id, user_id, order_status, total_amount)
VALUES (101, 1, 'PLACED', 2499.00);
This works because user 1 exists.
But this will fail:
INSERT INTO orders (id, user_id, order_status, total_amount)
VALUES (102, 999, 'PLACED', 1500.00);
The database rejects it because user 999 does not exist.
This is the value of a foreign key. It prevents invalid relationships.
Real Example: Products and Order Items
Orders become more interesting when one order can contain multiple products.
A beginner may try to store product names directly inside the orders table. That works for one product, but it fails when an order has many products.
A better design uses an order items table.
CREATE TABLE products (
id BIGINT PRIMARY KEY,
name VARCHAR(150) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock_quantity INT NOT NULL
);
CREATE TABLE order_items (
id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
price_at_purchase DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
This design has two foreign keys.
order_id connects each item to an order.
product_id connects each item to a product.
This allows one order to contain many products.
Example query:
SELECT
o.id AS order_id,
u.name AS customer_name,
p.name AS product_name,
oi.quantity,
oi.price_at_purchase,
o.total_amount
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.id = 101;
This query combines users, orders, order items, and products using primary key and foreign key relationships.
This is the practical power of relational databases.
The Relationship System Inside Joins
Joins are easier to understand when you understand keys.
A join connects rows from different tables based on related columns. In most real projects, those related columns are primary keys and foreign keys.
Example:
SELECT
users.name,
orders.id,
orders.total_amount
FROM users
JOIN orders ON users.id = orders.user_id;
The database matches users.id with orders.user_id.
Without proper keys, joins become unreliable. You may join using names or emails, but those values can change or duplicate. IDs are safer because they are designed for identity.
Primary keys and foreign keys make joins predictable.
One-to-One Relationship
A one-to-one relationship means one row in a table is connected to one row in another table.
Example: a user has one profile.
CREATE TABLE user_profiles (
id BIGINT PRIMARY KEY,
user_id BIGINT UNIQUE NOT NULL,
bio TEXT,
profile_image_url VARCHAR(255),
FOREIGN KEY (user_id) REFERENCES users(id)
);
The important part is UNIQUE.
user_id BIGINT UNIQUE NOT NULL
This ensures one user can have only one profile.
Without UNIQUE, one user could accidentally have many profile rows.
One-to-one relationships are useful when optional or large details are separated from the main table.
For example, basic user login data can stay in the users table, while profile biography and avatar information can stay in a separate user profiles table.
One-to-Many Relationship
A one-to-many relationship is the most common database relationship.
Examples:
One user has many orders.
One author has many posts.
One category has many products.
One course has many lessons.
One customer has many addresses.
Example: blog authors and posts.
CREATE TABLE authors (
id BIGINT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE posts (
id BIGINT PRIMARY KEY,
author_id BIGINT NOT NULL,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
status VARCHAR(30) NOT NULL,
FOREIGN KEY (author_id) REFERENCES authors(id)
);
Fetch posts with author name:
SELECT
posts.title,
authors.name AS author_name,
posts.status
FROM posts
JOIN authors ON posts.author_id = authors.id
WHERE posts.status = 'PUBLISHED';
This is a clean one-to-many relationship.
The author is stored once. Each post refers to the author using author_id.
Many-to-Many Relationship
A many-to-many relationship happens when many rows in one table can connect to many rows in another table.
Example: students and courses.
One student can enroll in many courses.
One course can have many students.
A direct foreign key is not enough. We need a junction table.
CREATE TABLE students (
id BIGINT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
id BIGINT PRIMARY KEY,
title VARCHAR(150) NOT NULL
);
CREATE TABLE enrollments (
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);
The enrollments table connects students and courses.
This table uses a composite primary key:
PRIMARY KEY (student_id, course_id)
That means the same student cannot enroll in the same course twice.
This is a powerful design pattern. Many-to-many relationships are common in real applications.
Examples:
Users and roles
Posts and tags
Products and categories
Students and courses
Employees and projects
Composite Primary Key
A composite primary key uses more than one column to identify a row.
In the enrollments table, neither student_id alone nor course_id alone is unique. But together, they form a unique enrollment.
PRIMARY KEY (student_id, course_id)
This is useful for junction tables.
Another example: product warehouse stock.
CREATE TABLE warehouse_stock (
warehouse_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (warehouse_id, product_id)
);
A product can exist in many warehouses. A warehouse can store many products. But the combination of warehouse and product should be unique.
Composite keys are useful, but they can make foreign key references longer. Some teams prefer a separate surrogate id column and a unique constraint on the pair. Both designs can be valid depending on the project.
Surrogate Key and Natural Key
A surrogate key is an artificial ID created only for the database.
Example:
id BIGINT PRIMARY KEY
A natural key is a real-world value used as identity.
Examples:
Email address
Passport number
Phone number
Vehicle registration number
Product SKU
At first, natural keys may look attractive. For example, email seems unique for users.
But real-world values can change.
A user may change their email. A company may change product SKU rules. A phone number may be reassigned. A student roll number may follow institution-specific rules.
This is why many applications use surrogate primary keys such as numeric IDs or UUIDs.
The natural value can still be unique, but it does not have to be the primary key.
Example:
CREATE TABLE users (
id BIGINT PRIMARY KEY,
email VARCHAR(150) UNIQUE NOT NULL
);
Here, id is the primary key and email is a unique business value.
This is a professional design approach.
Auto Increment IDs
Many SQL databases support auto-generated numeric IDs.
Example:
CREATE TABLE categories (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE
);
In MySQL-style syntax, it may look like:
CREATE TABLE categories (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE
);
Auto increment IDs are simple, readable, and efficient.
They are commonly used in traditional web applications.
However, auto increment IDs can reveal record counts in public URLs. For example, /orders/1000 may suggest there are at least 1000 orders. For many apps, this is not a serious issue. For some systems, it may matter.
This is one reason some systems use UUIDs for public identifiers.
UUID Primary Keys
A UUID is a long unique identifier.
Example:
550e8400-e29b-41d4-a716-446655440000
UUIDs are useful when records are created across distributed systems, microservices, mobile clients, or multiple databases.
A UUID can be generated without depending on a central auto-increment sequence.
Example table:
CREATE TABLE files (
id UUID PRIMARY KEY,
file_name VARCHAR(200) NOT NULL,
storage_path VARCHAR(255) NOT NULL
);
UUIDs are useful, but they also have trade-offs. They are longer than numeric IDs. They can make indexes larger. They are less readable in logs and debugging.
A practical approach is to use numeric IDs for internal tables and UUIDs for public-facing identifiers when needed.
Example:
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
public_id UUID UNIQUE NOT NULL,
user_id BIGINT NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
The backend can use id internally and expose public_id in URLs.
This is a useful production pattern many beginners do not know.
Referential Integrity
Referential integrity means the database protects relationships between tables.
If an order refers to a user, that user should exist.
If an order item refers to a product, that product should exist.
If a comment refers to a blog post, that blog post should exist.
Foreign keys enforce this protection.
Without referential integrity, orphan records can appear.
An orphan record is a child record that refers to a missing parent.
Example:
orders.user_id = 50
But there is no user with ID 50.
That order is now disconnected from a valid user. Reports may break. Admin panels may show missing data. Backend logic may fail.
Foreign keys prevent this problem.
Delete Rules in Foreign Keys
Foreign keys can define what happens when a parent row is deleted.
Common options include:
RESTRICT
CASCADE
SET NULL
NO ACTION
These options are important and often misunderstood.
Restrict Delete
Restrict means the parent row cannot be deleted if child rows exist.
Example:
A user cannot be deleted if they have orders.
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE RESTRICT
);
This protects order history.
For business systems, restrict is often safer than cascade.
Cascade Delete
Cascade means when the parent row is deleted, related child rows are also deleted.
Example:
If a blog post is deleted, its comments can also be deleted.
CREATE TABLE comments (
id BIGINT PRIMARY KEY,
post_id BIGINT NOT NULL,
comment_text TEXT NOT NULL,
FOREIGN KEY (post_id) REFERENCES posts(id)
ON DELETE CASCADE
);
Cascade is useful when child data has no meaning without the parent.
But cascade should be used carefully. A wrong delete can remove many related records.
Set Null
Set null means when the parent is deleted, the foreign key value becomes null.
Example:
A blog post may keep existing content even if the author account is removed.
CREATE TABLE posts (
id BIGINT PRIMARY KEY,
author_id BIGINT NULL,
title VARCHAR(200) NOT NULL,
FOREIGN KEY (author_id) REFERENCES authors(id)
ON DELETE SET NULL
);
This works only when the foreign key column allows null values.
Set null is useful when the child record can still exist without the parent.
Null Foreign Keys
A foreign key can be nullable.
This means the relationship is optional.
Example: an employee may or may not have a manager.
CREATE TABLE employees (
id BIGINT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
manager_id BIGINT NULL,
FOREIGN KEY (manager_id) REFERENCES employees(id)
);
Here, manager_id is a foreign key that refers to the same table. This is called a self-referencing foreign key.
Top-level managers may have manager_id as null.
This design is common in organization charts, category trees, comment replies, and referral systems.
Self-Referencing Foreign Key
A self-referencing foreign key connects a table to itself.
Example: categories and subcategories.
CREATE TABLE categories (
id BIGINT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
parent_id BIGINT NULL,
FOREIGN KEY (parent_id) REFERENCES categories(id)
);
This allows category structures like:
Electronics
Mobile Phones
Laptops
Fashion
Men
Women
Each subcategory points to its parent category using parent_id.
Self-referencing keys are powerful, but they must be managed carefully to avoid circular relationships.
For example, a category should not become its own parent.
Indexes on Foreign Keys
A lesser-known production point is that foreign key columns often need indexes.
Many developers create foreign keys but forget query performance.
Example:
SELECT * FROM orders WHERE user_id = 25;
This query is common because applications often fetch orders for one user.
An index helps:
CREATE INDEX idx_orders_user_id ON orders(user_id);
Foreign keys protect relationships. Indexes improve lookup speed.
Some databases automatically create certain indexes for constraints, while others may not create all useful indexes for foreign key lookups. Developers should check their database engine and query patterns.
This is important in production. A foreign key without a useful index can make joins and deletes slower as data grows.
Unique Constraint and Primary Key Difference
A primary key and a unique constraint both prevent duplicate values, but they are not exactly the same.
A primary key identifies the row. It cannot be null. A table should have only one primary key.
A unique constraint prevents duplicate values in a column or group of columns. A table can have multiple unique constraints.
Example:
CREATE TABLE users (
id BIGINT PRIMARY KEY,
email VARCHAR(150) UNIQUE NOT NULL,
phone_number VARCHAR(20) UNIQUE
);
Here, id is the primary key. Email and phone number are unique business values.
This is common in authentication systems.
The backend may use id internally, while login may use email.
Foreign Key and Unique Constraint Together
Sometimes a foreign key should also be unique.
This creates a one-to-one relationship.
Example: one user has one account setting row.
CREATE TABLE user_settings (
id BIGINT PRIMARY KEY,
user_id BIGINT UNIQUE NOT NULL,
theme VARCHAR(30) DEFAULT 'light',
notification_enabled BOOLEAN DEFAULT TRUE,
FOREIGN KEY (user_id) REFERENCES users(id)
);
The foreign key connects settings to the user. The unique constraint ensures one settings row per user.
Without the unique constraint, one user could have multiple settings rows.
This is a small but important design detail.
Soft Delete and Foreign Key Problems
Soft delete means records are not physically deleted. Instead, a column marks them as deleted.
Example:
ALTER TABLE users
ADD COLUMN deleted_at TIMESTAMP NULL;
A user is deleted by setting deleted_at.
UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = 10;
Soft delete is useful when data should be recoverable or auditable.
But soft delete creates relationship questions.
If a user is soft-deleted, should their orders remain visible? Should they be hidden from admin reports? Should new orders be blocked? Should their email be reusable?
Foreign keys do not automatically understand soft delete logic. The application must handle it carefully.
This is unknown information many beginners miss. Foreign keys protect physical relationships, but business rules like soft deletion need additional design.
Primary Keys in API Design
Primary keys often appear in API design.
Example:
GET /products/15
PUT /products/15
DELETE /products/15
Here, 15 identifies the product.
But exposing internal numeric IDs is not always ideal for sensitive resources.
For public-facing URLs, some applications use slugs or UUIDs.
Example:
GET /posts/sql-vs-nosql-databases
GET /orders/550e8400-e29b-41d4-a716-446655440000
A slug is readable for public content. A UUID is better for private resources where predictable IDs may be risky.
Database design and API design are connected. A good developer thinks about both.
Primary Keys and Security
Primary keys can create security issues if the backend does not check authorization.
Example:
A user opens:
/orders/101
Then changes it to:
/orders/102
If the backend only checks whether order 102 exists, the user may see another user’s order.
This is not a primary key problem. It is an authorization problem.
The backend should check both the primary key and ownership.
Example query:
SELECT *
FROM orders
WHERE id = 102
AND user_id = 25;
This ensures user 25 can access only their own order.
Primary keys help find records, but security rules decide whether the current user is allowed to access them.
This is an important real-world point.
Foreign Keys and Reporting
Foreign keys make reporting easier because related data is connected cleanly.
Example: monthly sales by category.
SELECT
c.name AS category_name,
SUM(oi.quantity * oi.price_at_purchase) AS total_sales
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
JOIN orders o ON oi.order_id = o.id
WHERE o.order_status = 'PAID'
GROUP BY c.name
ORDER BY total_sales DESC;
This report uses multiple relationships.
Order items connect to products. Products connect to categories. Order items connect to orders.
Without proper keys and relationships, this report becomes difficult and unreliable.
Business reporting is one reason relational database design matters.
Foreign Keys in Authentication and Roles
Many applications use users and roles.
A user may have one role or many roles.
Simple one-role design:
CREATE TABLE roles (
id BIGINT PRIMARY KEY,
name VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE users (
id BIGINT PRIMARY KEY,
email VARCHAR(150) UNIQUE NOT NULL,
role_id BIGINT NOT NULL,
FOREIGN KEY (role_id) REFERENCES roles(id)
);
Many-to-many role design:
CREATE TABLE user_roles (
user_id BIGINT NOT NULL,
role_id BIGINT NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (role_id) REFERENCES roles(id)
);
The second design is useful when one user can have multiple roles, such as admin, editor, and reviewer.
Keys make permission systems more structured.
Foreign Keys in Blog Systems
A blog platform can use keys to connect posts, authors, categories, tags, and comments.
CREATE TABLE posts (
id BIGINT PRIMARY KEY,
author_id BIGINT NOT NULL,
category_id BIGINT NOT NULL,
title VARCHAR(200) NOT NULL,
slug VARCHAR(220) UNIQUE NOT NULL,
content TEXT NOT NULL,
FOREIGN KEY (author_id) REFERENCES users(id),
FOREIGN KEY (category_id) REFERENCES categories(id)
);
Tags usually need a many-to-many structure.
CREATE TABLE post_tags (
post_id BIGINT NOT NULL,
tag_id BIGINT NOT NULL,
PRIMARY KEY (post_id, tag_id),
FOREIGN KEY (post_id) REFERENCES posts(id),
FOREIGN KEY (tag_id) REFERENCES tags(id)
);
This design allows one post to have many tags and one tag to belong to many posts.
This is better than storing tags as comma-separated text because it supports clean filtering and reporting.
Bad Design: Comma-Separated Foreign Keys
A common beginner mistake is storing multiple IDs in one column.
Bad design:
CREATE TABLE posts (
id BIGINT PRIMARY KEY,
title VARCHAR(200),
tag_ids VARCHAR(100)
);
Example value:
1,3,7
This looks easy, but it creates problems.
The database cannot properly enforce foreign keys. Searching becomes difficult. Joins become messy. Updating tags becomes error-prone.
A junction table is the correct design.
CREATE TABLE post_tags (
post_id BIGINT NOT NULL,
tag_id BIGINT NOT NULL,
PRIMARY KEY (post_id, tag_id),
FOREIGN KEY (post_id) REFERENCES posts(id),
FOREIGN KEY (tag_id) REFERENCES tags(id)
);
This is a high-value database design lesson. Never store multiple foreign keys as comma-separated strings.
Bad Design: Using Names as Foreign Keys
Another beginner mistake is connecting tables using names.
Bad idea:
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_name VARCHAR(100)
);
Names are not reliable identifiers.
Two users can have the same name. A user can change their name. Spelling can vary.
Better design:
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
Names are display values. IDs are identity values.
This distinction is important in professional database design.
Bad Design: Missing Foreign Keys
Some applications create columns like user_id but do not define an actual foreign key constraint.
Example:
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL
);
This table has a user_id column, but the database does not know it refers to users.
The application may still work for some time, but invalid user IDs can enter the table.
A proper design adds the constraint:
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id);
A column name alone does not create a relationship. A foreign key constraint creates the relationship.
Naming Foreign Key Constraints
Naming constraints is useful for debugging and migrations.
Instead of letting the database generate random constraint names, developers can name them clearly.
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
A clear name tells what the relationship means.
If an error occurs, the database may show the constraint name. A meaningful name makes debugging easier.
Good naming patterns:
fk_orders_user
fk_order_items_order
fk_order_items_product
fk_posts_author
fk_posts_category
Clear names help teams maintain the database.
Primary Keys and Database Migrations
In real projects, tables are created and changed through migrations.
A migration is a controlled database change.
Example migration:
CREATE TABLE payments (
id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
payment_status VARCHAR(30) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
CONSTRAINT fk_payments_order
FOREIGN KEY (order_id)
REFERENCES orders(id)
);
Migrations help track database history.
They make it clear when tables, keys, constraints, and indexes were added.
This is important when multiple developers work on the same project or when the app has development, staging, and production environments.
Primary Key and Foreign Key in Spring Boot Projects
In Java Spring Boot applications, primary key and foreign key relationships often appear in entity classes.
A simple User entity:
@Entity
public class User {
@Id
private Long id;
private String name;
private String email;
}
An Order entity:
@Entity
public class Order {
@Id
private Long id;
@ManyToOne
private User user;
private BigDecimal totalAmount;
}
The @ManyToOne relationship means many orders can belong to one user.
In real projects, entity relationships should be designed carefully. Poor ORM mapping can create performance problems such as too many queries.
Database design should be understood even when using ORM tools like Hibernate.
ORM does not remove the need to understand primary keys and foreign keys.
Performance Problem: N Plus One Queries
A lesser-known issue in ORM-based applications is the N plus one query problem.
Example: the application fetches 100 orders. Then for each order, it separately fetches the user.
That can become 101 queries.
One query for orders.
100 extra queries for users.
This is not a primary key problem, but it happens around relationships.
A better approach may use a join query or fetch optimization.
SQL example:
SELECT
o.id,
o.total_amount,
u.name
FROM orders o
JOIN users u ON o.user_id = u.id;
Understanding keys and joins helps developers avoid ORM performance issues.
This is useful knowledge for backend developers.
Keys and Data Cleanup
Primary keys and foreign keys also help data cleanup.
Suppose an admin wants to find users who have never placed an order.
SELECT
u.id,
u.name,
u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
This query uses the relationship between users and orders.
Another example: find products that were never ordered.
SELECT
p.id,
p.name
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.product_id IS NULL;
These queries help business teams analyze unused data, inactive users, and product performance.
Without clean keys, this kind of analysis becomes difficult.
Keys and Audit Trails
Some applications need audit trails.
An audit trail records who performed an action.
Example:
CREATE TABLE audit_logs (
id BIGINT PRIMARY KEY,
user_id BIGINT NULL,
action VARCHAR(100) NOT NULL,
entity_name VARCHAR(100) NOT NULL,
entity_id BIGINT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
This table can record actions such as product updated, order cancelled, user role changed, or payment refunded.
In audit logs, the foreign key may be nullable because some actions may come from system jobs instead of users.
Audit design is an advanced real-world use case for keys.
Keys and Multi-Tenant Applications
In multi-tenant applications, one system serves many companies or organizations.
Example: a SaaS app used by many schools.
Tables may include tenant_id.
CREATE TABLE tenants (
id BIGINT PRIMARY KEY,
name VARCHAR(150) NOT NULL
);
CREATE TABLE students (
id BIGINT PRIMARY KEY,
tenant_id BIGINT NOT NULL,
name VARCHAR(100) NOT NULL,
FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);
Queries must include tenant filtering.
SELECT *
FROM students
WHERE tenant_id = 5;
This prevents data mixing between organizations.
In multi-tenant systems, primary keys and foreign keys are not only about structure. They also support data isolation and security.
Practical Design Checklist
Before designing tables, think about identity and relationships.
Every important table should have a clear primary key.
Every relationship should be represented using a foreign key when relational integrity matters.
Use surrogate keys when real-world values may change.
Use unique constraints for business rules such as email, SKU, or slug.
Use junction tables for many-to-many relationships.
Avoid comma-separated IDs.
Avoid using names as identifiers.
Choose delete rules carefully.
Index foreign key columns based on query patterns.
Do not expose internal IDs without authorization checks.
Understand soft delete behavior.
Use clear constraint names.
Plan migrations carefully.
These decisions make a database easier to maintain as the application grows.
Practical Mindset for Developers
Primary keys and foreign keys are not just exam definitions. They are the foundation of relational database design.
A primary key gives each row a clear identity. A foreign key connects that identity to another table. Together, they help databases protect relationships, prevent invalid records, support joins, improve reporting, and make applications more reliable.
A beginner may think keys are only needed because SQL syntax requires them. A professional developer understands that keys protect the application’s data model.
When a user places an order, when a student joins a course, when a post receives comments, when a payment belongs to an invoice, and when an employee reports to a manager, primary keys and foreign keys quietly keep those relationships organized.
Good database design starts with one simple question:
Which record is this, and how is it connected to other records?
That question is the heart of primary key and foreign key design.
Post a Comment